Expression for displaying Average of multiple fields in TextBox 3

[dmuroff]

Hi,
For each record on a form I have multiple prices (NYPrice, MAPrice, ALPrice, NJPrice etc).

I would like to have a seperate unbound text box on the form which calculates and displays the average of these prices.

Thanks in advance!

Dan

 

[TheAceMan1]

How are ya dmuroff . . . . .

=Sum(Nz(Me![purple]TextBoxName1[/purple], 0) + .... Nz(Me![purple]TextBoxNameN[/purple], 0))/[purple]NumberOfControls[/purple]

See Ya! . . . . . .

 

[dmuroff]

Hey there Ace,

Here's what I put into the control source of the text box:

=Sum(nz(me!text114,0)+nz(me!text116,0)+nz(me!text118,0)+nz(me!text120,0)+nz(me!text122,0))/5

Is it a problem if text114,text116,text118,text120 or text122 may be null?

Would I have to assign a default value of '0' for this to work?

Because I am getting a #error in the textbox as it is.
Thanks!

 

[Onyxpurr]

That's what your NZ formula is for. It will replace the null value with a 0.

NZ(textbox, 0)

 

[PHV]

Either try this:
=(nz(me!text114,0)+nz(me!text116,0)+nz(me!text118,0)+nz(me!text120,0)+nz(me!text122,0))/5
Or set the value in the Current event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

dmuroff . . . . .

So sorry . . . . I used [blue]Sum()[/blue] out of habit . . . my mistake . . . [blue]PHV[/blue] has the proper [blue]format/syntax[/blue] and deserves the star more than I. [blue]Onyxpurr[/blue] targeting is on bulls-eye . . . . [blue]Nz[/blue] takes care of values not entered by the user.

See Ya! . . . . . .