IIf vs Switch

[RYOUAPHI]

I am writing a query in my DB.

First I take two values from a table and divide them by each other. Based on the sum a certain value will be added to another column depending on the value range this new value falls between. Here's where it gets tricky for me. If both of the original 2 numbers are 0, or the first of the two numbers are 0, then the answer is infinite. I need to have a solid number 0 or greater to be able to assign a value to it. So lets say num1 is 0 and num2 is 10 or 0.

10/0=infinite or
0/0=infinite

How do I do this calculation so I dont get an infinite number and that should resolve the next step for me as well.

In addition, what if I am working on a null value instead of a 0

 

[Brianwarnock]

There is no magic solution. YOU have to decide what you want to do in any given circumstance, after all it is easy to check for Nulls or 0 and take the required action.

Brian

 

[khawar]

Use this calculation to get 0 even if the number is divided by 0

IIf(IsNull([num2]) Or [num2]=0,0,[Num1]/[Num2])

 

[MSAccessRookie]

Use this calculation to get 0 even if the number is divided by 0

IIf(IsNull([num2]) Or [num2]=0,0,[Num1]/[Num2])

Or possibly modify this compound IIf Statement if it does what you want.

[FONT=Times New Roman][SIZE=3]IIf(Denom=0, {ValueifInfinite}, IIf(Numer/Denom<= {ValuetoCompare}, {ValueifnotHigher}, {ValueifHigher}))[/SIZE][/FONT]