#Num, how to get a 0 value instead of #Num

[Czeszyn]

I am getting a #Num! because sometimes there is no data to be entered. The formula I have does work, but what do I need to add to it so that it will produce a 0 value if no data is required.

[TotScrwBlank]/[TotalAmount]

Also, by adding anything to this formula, would this make me lose my data that is in the database already?

I hope that some one can help.

Thank you in advance.
Tony:banghead:

 

[Ranman256]

iif ([TotalAmount]) = 0,0,[TotScrwBlank]/[TotalAmount])

 

[plog]

Division is the ficklest of all simple math operations. You need to test your numerator and denominator. Make sure the numerator is a number and the denominator is a non-0 number.

You would use the IsNumeric function (http://www.techonthenet.com/access/functions/advanced/isnumeric.php) and the Iif function (http://www.techonthenet.com/access/functions/advanced/iif.php)

 

[Czeszyn]

Hi Ranman256
Thank you for responding. I tried what you said and I get the following:

The expression you entered has a function containing the wrong number of arguments.

I checked to make sure I entered it as you have it too.

Tony.

 

[jdraw]

czeszyn,

I think ranman just had an extra bracket.
Here is his suggestion (revised)

iif ([TotalAmount] = 0,0,[TotScrwBlank]/[TotalAmount])

 

[plog]

No data is different than a value of 0. I'm pretty sure once you get the syntax right Ran's method will fail for records where either of those fields is Null.

 

[Czeszyn]

Thank you everyone, that did fix my error. I noticed that I had another one like that:

[TotScrwBlank]/([TotScrwBlank]+[TotParts])*1000000

so would I rewrite that as:

iff(([TotScrwBlank]+[TotParts])*1000000)=0,0,
[TotScrwBlank]/([TotScrwBlank]+[TotParts])*1000000)

Thank you again.
Tony

 

[Czeszyn]

Thank you I got it to work. You all have been a great help.

Tony