#Num! error in my formula

[Eastbay2]

Hello,



I'm receiving this error "#Num!" in my formula within Access. Here is my formula:


FW Ratio 30/50: Format(Nz([Store_level_Markdown_3]![FW 30% UNIT])/(Nz([Store_level_Markdown_3]![FW 30% UNIT])+Nz([Store_level_Markdown_3]![FW 50% UNIT])),"0%") & " / " & Format(1-(Nz([Store_level_Markdown_3]![FW 30% UNIT])/(Nz([Store_level_Markdown_3]![FW 30% UNIT])+Nz([Store_level_Markdown_3]![FW 50% UNIT]))),"0%")

My issue, in a few rows of data, is caused by me trying to divide zero by zero. It has taken me a while to get the formula where it is now and i can’t seem to solve this issue. I'm fine with it just saying "0", "-" or "None" or anything else in this situation other than the error.

Thanks.

[orange]

Check your use of the Nz function, I don't think you have a "value-if-null" .

see http://www.techonthenet.com/access/f...dvanced/nz.php

[ssanfu]

Forgetting about the Format function and the NZ function, you have two division formulas. If either field [FW 30% UNIT] or [FW 50% UNIT] is NULL or Zero, the division will fail.

So first you have to check if both of the fields are either NULL or Zero. I would use the IIF() function. If both of the fields are either NULL or Zero, then return a zero. If one of the two fields is not NULL or not zero, then do the calc.

Try this in your query:

FW Ratio 30/50: iif(nz([Store_level_Markdown_3]![FW 30% UNIT],0)=0 AND nz([Store_level_Markdown_3]![FW 50% UNIT],0)=0,0,Format(Nz([Store_level_Markdown_3]![FW 30% UNIT],0)/(Nz([Store_level_Markdown_3]![FW 30% UNIT],0)+Nz([Store_level_Markdown_3]![FW 50% UNIT],0)),"0%") & " / " & Format(1-(Nz([Store_level_Markdown_3]![FW 30% UNIT],0)/(Nz([Store_level_Markdown_3]![FW 30% UNIT],0)+Nz([Store_level_Markdown_3]![FW 50% UNIT],0))),"0%"))