Totals problem on form footer

[kbestle]

I have a form that shows information for the years 2004, 2005 and 2006. The information is how many of a part were replaced on an aircraft in that year. This qty is then divided into the # of flight hours giving the average time installed. I then average this avg. in the form footer. Everything works OK unless one of the years has no qty. I have used an IIF statement to test for this and plug in a fixed value for that year. When I come to a record that has a year with no qty I get #Error in the Total field. I have tried doing the calculation in the underlying query and on the form itself with no sucess.

Year Qty Flight Hours ATI
2004 2 1000 500
2005 3 1200 400
2006 5 1500 300
Avg. 400

TIA

 

[HandsOnAccess]

You have to make use of Nz() function. Nz function converts a Null value and prevent it from propagating through an expression.

In case there is no qty for a particular year then Nz function will return zero.

Example

x = Null
y = 5

z = x + y 
msgbox z 
'You will get "Invalid use of Null"

z = Nz(x) + Nz(y)
msgbox z
'You will get "5"

 

[PHV]

I get #Error in the Total field
What is the ControlSource of the Total control (or the expression in the underlaying query of the Total field) ?

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

 

[kbestle]

The ControlSource for the control is Avg([ATI])

The expression in the query is IIf(Isnull([qty]),0,[qty]) When a year has no qty this is what the data looks like.

Year Qty Flight Hours ATI
2004 2 1000 500
2005 0 1200 1200
2006 5 1500 300
Avg. #Error

 

[PHV]

What is the SQL code of the query ?
I suspect a divide by zero somewhere ...

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

 

[HandsOnAccess]

I have simulated your scenario, It worked for me.
Assuming You have trapped Divide by Zero Error

Case-1
Please go to design mode and cross-check the control name you have used for ATI, It should be same as what you specified in AVG() function.

Case-2
Alternatively, in Footer Average Control you can also place the control source as =Avg(IIf([Qty]>0,[FlightHours]/[Qty],[FlightHours]))

Change Field name wherever necessary
PS: Also Re-Note for Qty = 0 ATI is 1200!