How average up to 5 numbers on a form

I have a form with up to 5 number fields.  Some of them may have a value and some may not.  How can I average them no matter how many have a value?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Depends.  I use a function that will accept any number of parameters.  Are these fields in a table or unbound controls?  How do you want to handle the fields that have no data?  The function I use accepts an array and loops through the array

Public Function fnAvg(ParamArray ValList() As Variant) As Variant

    Dim intLoop As Integer
    Dim dblSum As Double, intCount As Integer
    
    For intLoop = LBound(ValList) To UBound(ValList)
    
        If Not IsNull(ValList(intLoop)) Then
        
            dblSum = dblSum + ValList(intLoop)
            intCount = intCount + 1
            
        End If
        
    Next
    
    If intCount = 0 Then
        fnAvg = Null
    Else
        fnAvg = dblSum / intCount
    End If
    
End Function

Open in new window

This returns a NULL value if there are no non-null values in the array.  If the 5 fields are in a table and bound to that table, then you could just bind your form to a query that includes a computed column and that column would update automatically when any of the other controls is changed.

If the controls are unbound, then you would set the Control Source of the Average field to: =fnAvg([Forms]![formName]!field1, [Forms]![formName]!field2, [Forms]![formName]!field3, [Forms]![formName]!field4, [Forms]![formName]!field5)
0
SteveL13Author Commented:
The fields are bound to the table but probably aren't written to the table as the user enters them.  So I'd like the average to be calculated as they enter the values.

I'm using your code.  Plus I have this in the control source of the average field:

=fnAvg(Nz([txtSource1Rate],""),Nz([txtSource2Rate],""),Nz([txtSource3Rate],""),Nz([txtSource4Rate],""),Nz([txtSource5Rate],""))

But I get a type mismatch error on the line:

dblSum = dblSum + ValList(intLoop)

If it matters the values are currency numbers.
0
Dale FyeCommented:
If they are bound to the table and the form is bound to the table, then they will be written to the fields as the user enters them.  If you use a query for the recordsource of the form, then the computed column will automatically be updated as each entry is made.

Are you typing in the $ or do you simply have the controls formatted as currency?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SteveL13Author Commented:
The table is the recordsource for the form.  The controls are formatted as currency.  I am not typing the $.
0
SteveL13Author Commented:
And I put your code in a public module.  Correct?
0
Dale FyeCommented:
I tried entering values with the leading $ and that worked fine.  And it even worked when I manually entered commas as thousands separators and it worked then too.
0
Dale FyeCommented:
You might try changing:

If Not IsNull(ValList(intLoop)) Then

to:

If Trim(ValList(intLoop) & "") <> "" Then

that will check for zero length strings as well as NULLs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
I don't get it.

Again, I have this in a module:

Public Function fnAvg(ParamArray ValList() As Variant) As Variant

    Dim intLoop As Integer
    Dim dblSum As Double, intCount As Integer

    For intLoop = LBound(ValList) To UBound(ValList)

        If Not IsNull(ValList(intLoop)) Then

            dblSum = dblSum + ValList(intLoop)
            intCount = intCount + 1

        End If

    Next

    If intCount = 0 Then
        fnAvg = Null
    Else
        fnAvg = dblSum / intCount
    End If
    
End Function

Open in new window


And I have this in the control source for the field to be the calculated field:

=fnAvg(Nz([txtSource1Rate],""),Nz([txtSource2Rate],""),Nz([txtSource3Rate],""),Nz([txtSource4Rate],""),Nz([txtSource5Rate],""))

And the five fields a bound to the table.

What am I doing wrong?
0
SteveL13Author Commented:
This did it:

You might try changing:

 If Not IsNull(ValList(intLoop)) Then

 to:

 If Trim(ValList(intLoop) & "") <> "" Then

 that will check for zero length strings as well as NULLs.
0
SteveL13Author Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.