calculate average on form in text box (1 Viewer)

[dmyoungsal]

I am creating a form that will be used to estimate transportation costs. I am using one table containing "zones" (different mileage ranges and an estimated average speed).

On my form, I am allowing a user to enter up to 4 different types of equipment, a quantity, and a zone. Using DLOOKUP, I am grabbing the average speed within that particular zone. All this is good (still fleshing out the process).

The stumbling block at the point is how to calculate the average of the average speeds. I was adding the 4 fields together (which works), but I cannot figure out the average calculation. I have tried =AVG([txtAvgMPH1]+[txtAvgMPH2]). This gives be an "Error#". If i try =AVERAGE..., I get the error #NAme?.

What is the best solution?

 

[arnelgp]

from which code are you using the Dlookup function.
you can insert a code in there:

dim dblAVG as double
dim i as integer
dim cnt as integer

'number of AvgMPH textbox you have (on your sample there are 4)
for i = 1 to 4
if trim(Me.Controls("txtAVgMPH" & i).Value & "") <> "" then
dblAVG = dblAVG + Me.Controls("txtAvgMPH" & i).Value
cnt = cnt + 1
end if
next
id dblAVG<> 0 then dblAVG = dblAVG/cnt
Me.yourTextBoxToDisplayAvg = dblAVG

 

[dmyoungsal]

=DLookUp("[ZAMPH]","qryTranZones","[ZONE] = form!txtzone2")

As I said, my dlookup works fine. I am just needing to figure out how to get an average of the four txtAvgMPH boxes.

I am working on using Nz, but having an issue with the issue of when there is only one entry made and the other three entries are blank.

 

[arnelgp]

create a public function in your form:

public function calcAVgMPH()
dim dblAVG as double
dim i as integer
dim cnt as integer

'number of AvgMPH textbox you have (on your sample there are 4)
Me.Recalc
for i = 1 to 4
if trim(Me.Controls("txtAVgMPH" & i).Value & "") <> "" then
dblAVG = dblAVG + Me.Controls("txtAvgMPH" & i).Value
cnt = cnt + 1
end if
next
id dblAVG<> 0 then dblAVG = dblAVG/cnt
Me.yourTextBoxToDisplayAvg = dblAVG
End function


now on each zone textboxes, after update event:

=calcAVgMPH()