#num!- Need Help

[sunaamu]

Question..I am getting the error #Num! on one of my reports... Im guessing its because there are no values in this particular field to avg. The expression I am using is:

=(Sum(IIf(Abs([Personnel Date])<>0,DateDiff("d",[Personnel Date],[Personnel Out Date]),0))/DCount("[DocumentID]","SOP_Tracking_Rpt_Qry","[Personnel In] = True"))

Any ideas on how I can make it where it produces zero?

 

[wazz]

Nz (nulls) function could help with that.

=Nz(Sum(IIf(Abs([Personnel Date])<>0,DateDiff("d",[Personnel Date],[Personnel Out Date]),0))/DCount("[DocumentID]","SOP_Tracking_Rpt_Qry","[Personnel In] = True")),0)

not sure if it's in the right place. you could use it on all of the "internal" calculations as well/instead.

 

[DCrake]

=(Sum(IIf(Abs([Personnel Date])<>0,DateDiff("d",[Personnel Date],[Personnel Out Date]),0))/Nz(DCount("[DocumentID]","SOP_Tracking_Rpt_Qry","[Personnel In] = True",0)))

The #Num is becuse you may be getting a null response from your dcount.

CodeMaster:

 

[sunaamu]

Still having problems...WAZZ, I tried using ur method first... I got an error saying invalid sytax or that I needed to enclose txt data (just adding () in front of Nz... it fixed the syntax error, but still getting #Num....

DCrake, I tried your method and received a msg saying that my function contains the wrong number of arguments...

 

[wazz]

try dcrake's again with a small change:

=(Sum(IIf(Abs([Personnel Date])<>0,DateDiff("d",[Personnel Date],[Personnel Out Date]),0))/Nz(DCount("[DocumentID]","SOP_Tracking_Rpt_Qry","[Personnel In] = True"),0)))

 

[sunaamu]

Still no luck...Is there a way that I can possibly rewrite the expression?

 

[wazz]

perhaps remove abs(). abs requires a number and it might be getting a string from [Personnel Date]. also not sure why sum() is in there.

 

[sunaamu]

ok...I'll try that