#num!

[lnoles]

How do I keep ' #num! ' from showing up in my reports when figuring Percentages. It shows up when there is a 'divide by zero' situation. I would prefer to see a zero or empty field instead.


[This message has been edited by lnoles (edited 12-28-1999).]

 

[R. Hicks]

Give us a sample of how and where you are getting the percent. Is this a result from code behind the form or report. A little more detail would make it easier to give you an exact answer over a gereral answer. This will not be hard to fix.

RDH

[This message has been edited by R. Hicks (edited 12-29-1999).]

[This message has been edited by R. Hicks (edited 12-29-1999).]

 

[lnoles]

The control source for the field is =[OC48 OH]/[OC48 AFF]*100 .
It is using the contents of 2 fields to get a percentage. The error shows up when both fields contain zero. I suppose I could avoid it by using some kind of IIF coding.

 

[Travis]

Add this private function to your report:

Private Function GetPercentage(lngNum As Long, lngDen As Long) As Long
On Error GoTo ErrorHandler
GetPercentage = lngNum / lngDen

Exit Function
ErrorHandler:
GetPercentage = 0
End Function


Now as the control Source of the Percentage field change it to as follows:

=GetPercentage([OC48 OH],[OC48 AFF])*100

By doing this this way it will calculate for each record (and best of all no IIF statements to fuss with)

 

[R. Hicks]

The only problem I see with Travis' approach would be if "OC48 OH" or "OC48 AFF" is a decimal entry. Then you would need to Declare lngNum and lngDen as either Single, Double or Currency. But it's still a good approach.

Ricky

 

[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?

 

[dsmalls]

How can i adjust the suggestion above to accommodate my expression?

=([pos13])/(([RC13])-([neg13]))

the result is a %.

Thanks

Nevermind,I figured it out.

 

[kplatt]

The reason is probably because a number divided by zero is undefined. does it show up in the table as Num# also? It sounds like you could make an expression to put in that control source that would show blank or zero when it comes back Num#. Let me know if you are interested and I can try to test something tomorrow. Kevin