andymacd
asked on
Problems creating an Average Function, get a "Type-declaration character does not match declared data type" error
All,
I'm trying to create a simple Average function that I can use on reports, but am constantly running into errors. I'm not very good at VBA, what you see below I pieced together from other snipets I found, so if the logic doesn't make sense, that's why! My end result would be a function that i could use as the row source for a report, for example: =Average([TableName],[Fiel
When I run this now I get the following error:
Compile error:
Type-declaration character does not match declared data type
Any help is greatly appreciated!
Regards,
Andrew
My code:
--------------------------
Function Average(tName$, fldName$) As Single
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim intTotal As Integer
Dim intCount As Integer
Dim ScoreAverage As Double
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("SELECT [" & fldName$ & "] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS NOT NULL ORDER BY [" & fldName$ & "];")
Do Until Rst.EOF
intTotal = intTotal + Rst!(fldName$) <<<<< THIS IS WHERE IT CRAPS OUT
intCount = intCount + 1
Loop
ScoreAverage = intTotal / intCount
End Function
--------------------------
I'm trying to create a simple Average function that I can use on reports, but am constantly running into errors. I'm not very good at VBA, what you see below I pieced together from other snipets I found, so if the logic doesn't make sense, that's why! My end result would be a function that i could use as the row source for a report, for example: =Average([TableName],[Fiel
When I run this now I get the following error:
Compile error:
Type-declaration character does not match declared data type
Any help is greatly appreciated!
Regards,
Andrew
My code:
--------------------------
Function Average(tName$, fldName$) As Single
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim intTotal As Integer
Dim intCount As Integer
Dim ScoreAverage As Double
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("SELECT [" & fldName$ & "] FROM [" & tName$ & "] WHERE [" & fldName$ & "] IS NOT NULL ORDER BY [" & fldName$ & "];")
Do Until Rst.EOF
intTotal = intTotal + Rst!(fldName$) <<<<< THIS IS WHERE IT CRAPS OUT
intCount = intCount + 1
Loop
ScoreAverage = intTotal / intCount
End Function
--------------------------
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the problem, as I see it, is the ! that you have after the Rst. To use the name of the field as teh identifier, you don't need the !. Like so....
rst(fldName$)
rst(fldName$)
If you are absolutely set on iterating through the entire recordset, change your code as follows:
Function MyAverage(tName$, fldName$) As Single
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim intTotal As Integer
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & "] IS NOT NULL")
intTotal = 0
Do Until Rst.EOF
intTotal = intTotal + Nz(Rst!(fldName),0) <<<<< THIS IS WHERE IT CRAPS OUT
Loop
MyAverage = intTotal / Rst.RecordCount
End Function
Function MyAverage(tName$, fldName$) As Single
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim intTotal As Integer
Set DB = CurrentDb()
Set Rst = DB.OpenRecordset("SELECT [" & fldName & "] FROM [" & tName & "] WHERE [" & fldName & "] IS NOT NULL")
intTotal = 0
Do Until Rst.EOF
intTotal = intTotal + Nz(Rst!(fldName),0) <<<<< THIS IS WHERE IT CRAPS OUT
Loop
MyAverage = intTotal / Rst.RecordCount
End Function
Overall, I would still recommend my first solution. You will have a HUGE savings on execution time over your current function.
Andrew,
If the end goal is to see an Average in a report, maybe you are re-inventing the wheel.
In any Access Report, if you group by any field, you can have a Group Footer visible on the Report. Place this formula in your Group Footer, to see the Average for all values in the detail rows for that Group:
=AVG([YourFieldName])
The SAME formula, placed in the Report Footer, would give you an Average for all the Groups, or all the detail records if there was no Grouping done in the Report.
HTH,
Tim
If the end goal is to see an Average in a report, maybe you are re-inventing the wheel.
In any Access Report, if you group by any field, you can have a Group Footer visible on the Report. Place this formula in your Group Footer, to see the Average for all values in the detail rows for that Group:
=AVG([YourFieldName])
The SAME formula, placed in the Report Footer, would give you an Average for all the Groups, or all the detail records if there was no Grouping done in the Report.
HTH,
Tim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help everyone! Not surprisingly I was reinventing the wheel as suggested, I appreciate the timely response routinet and thanks for your input as well GreymanMSC.
intTotal = intTotal + cint(rst(0))