Phrasing SQL to avoid 0/0

Denyial

Registered User.
Local time
Today, 08:32
Joined
Jul 29, 2015
Messages
24
I am using this field for a query:

Win Percentage: (IIf([ContractorNumbers] Is Null,0,[ContractorNumbers]))/(IIf([ContractorNumbers] Is Null,0,[ContractorNumbers])+IIf([BidderNumbers] Is Null,0,[BidderNumbers]))*100

Where [ContractorNumbers], [BidderNumbers] are both fields from other subqueries.

This works fine when [BidderNumbers] OR [ContractorNumbers] is 0, outputting 0 or 100% in either case, however if both [BidderNumbers] AND [ContractorNumbers] are 0, I get 0/0 and the query outputs #Num!

How can I change the field code to fix this?

OR, is there something else that I could do to fix the Bidder and Contractor queries that would avoid the 0/0 problem? Happy to link my database if that is the case.

THANKS!
 
How can I change the field code to fix this?

What resolution would you like? What should records show for the Win Percentage field when /0 is encountered?
 
Sorry it took so long to reply, you've been really helpful plog over this summer with my uselessness, so thank you.

N/A ? Or maybe just (nil) ? . What do you think?

Thank you!
 
Math nerds will get all high and mighty about how N/0 absolutely doesn't equal 0. But for every practical end-user I've come across they prefer it to resolve to 0. To accomplish this I build a custom function like so:

Code:
Public SafeDivision(in_N, in_D) As Double
    ' divides in_N by in_D, if division not possible, returns 0

Dim ret As Double=0				
    ' return value, default of 0

If(IsNumeric(in_N) AND IsNumeric(in_D) AND in_D<>0) Then ret = in_N/in_D

SafeDivision = ret

End Function


Then in a query I use it like so:

SalesPercentages: SafeDivision([UserSales], [TotalSales])

If you prefer "N/a" returned you can tweak SafeDivision to do that.
 

Users who are viewing this thread

Back
Top Bottom