Math Calculations

[George Too]

A subform has up to 30 fields with user-entered-numerical-data, some of these fields will be empty at times. In separate fields I need to calculate:

1. The average for these 30 fields not counting those that are empty.
2. Count only those fields that contain data.

Any takers?

Thank you.

ps. the data needs to be calculated before being entered in the tables.

 

[Rich]

You shouldn't store these calculated results, use a Totals query to give you the calculations you require

 

[George Too]

Hi Rich,
Can you elaborate a little more? Why shouldn't I store calculated results? Can I store the values though? How do I go about the Totals queries? Can this be done in code?

Thank you.

 

[George Too]

Any other takers on how can I perform my calculations using code? I have been searching the help system in Access but the examples are not close to what I want to do.

 

[bjackson]

Question.Are the 30 fields all one recordset or are they 30 recordsets

 

[George Too]

Hi bjackson

To answer your question, all 30 fields belong to the same recordset.

 

[DJN]

Here is a function to average fields in the same recordset.

'----------------------------------------------------
' Function RAvg() will average all the numeric arguments passed to
' the function. If none of the arguments are numeric, it will
' return a null value.
'-----------------------------------------------------
Dim dblTotal As Double
Dim lngCount As Long
Dim varArg As Variant
For Each varArg In FieldValues
If IsNumeric(varArg) Then
dblTotal = dblTotal + varArg
lngCount = lngCount + 1
End If
Next
If lngCount > 0 Then
RAvg = dblTotal / lngCount
Else
RAvg = Null
End If
End Function

 

[George Too]

Thanks, I'll try it today.

 

[George Too]

Variable not defined "FieldValues". I need more help using this function. Thanks.

 

[DJN]

George. If you are using a query, use a calculated field. e.g.

Average: RAvg([Ave1],[Ave2],[Ave3],[Ave4]............)

Where [Ave1] etc. are your field names (30 of them)
If you are not using a query, you will need an unbound text box with
=RAvg([Ave1],[Ave2],[Ave3],[Ave4]...........) as the control source.

Hope this helps

David

 

[George Too]

DJN,

I have taken the unbound text approach, but i keep getting the error: "The expression you entered has a function containing the wrong number of arguments." I have follow your syntax but something else seems to be not working. Any other pointers?

Thanks,