Get average of unbound textboxes.

[markhook]

I'm new to Access forms and was wondering what the easiest way to get an average of unbound text boxes. There could be up to 30 values, but some of them will be blank so I won't want to count them in the average (If not isnull?) Thanks in advance for your help. I have been doing forms like this in vb.net, but my new company insists on doing this using Access forms.

[matthewspatrick]

I would try to do this in VBA, and apply a naming convention to the controls (like tb1, tb2, tb3, ..., tb30).

For Counter = 1 To 30
    Numerator = Numerator + _
        Val(Nz(Me.Controls("tb" & Counter).Value, 0))
    Denominator = IIf(Trim(Me.Controls("tb" & Counter).Value) = "", _
        0, 1)
Next

If Denominator = 0 Then
    'div by zero.  Do something
Else
    Result = Numerator / Denominator
End If

[markhook]

Wow! That's fantastic Patrick, thanks. I didn't realize that I could use/call functions similar to what I was doing in VB.net. That code's going to save me tons of typing.

[matthewspatrick]

Glad to help!

BTW, I noticed the calc for the denominator is bad. Here is corrected code:

For Counter = 1 To 30 
    Numerator = Numerator + _ 
        Val(Nz(Me.Controls("tb" & Counter).Value, 0)) 
    Denominator = Denominator + IIf(Trim(Me.Controls("tb" & _
        Counter).Value) = "", 0, 1) 
Next 

If Denominator = 0 Then 
    'div by zero.  Do something 
Else 
    Result = Numerator / Denominator 
End If

[markhook]

Can you do a "numerator +=" and "denominator +=" instead of "num = num +" (abbr.)?

[matthewspatrick]

Can you do a "numerator +=" and "denominator +=" instead of "num = num +" (abbr.)?

No, VBA does not have that kind of operator.

[markhook]

OK, thanks.

[markhook]

Glad to help!

BTW, I noticed the calc for the denominator is bad. Here is corrected code:

For Counter = 1 To 30 
    Numerator = Numerator + _ 
        Val(Nz(Me.Controls("tb" & Counter).Value, 0)) 
    Denominator = Denominator + IIf(Trim(Me.Controls("tb" & _
        Counter).Value) = "", 0, 1) 
Next 

If Denominator = 0 Then 
    'div by zero.  Do something 
Else 
    Result = Numerator / Denominator 
End If

The debugger does not like the me. Do I have to make this a class module? If so, how? Thanks.

[matthewspatrick]

I had assumed the code was going into the code module for your form. If not, replace "Me" with a reference to the form:

Forms!MyForm