Query Expression Division Error - #Num! and #Div/0!

[BP2E]

My expression is subtracting the monthly charges vs budgeted charges, then dividing the total by the budgeted charges and shown as a percentage. If monthly charges and budgeted charges both have a zero total a "#Num!" returns. If only one of the data fields has zero total a "#Div/0!" returns. I would like to run an if statement which replaces all "#Num!" and "#Div/0!" values with "0.00%". I've tried the IIF statement but returns saying invalid syntax.
As you can tell, I solidly fall into the Beginner category of access.
Thanks!

Expr4: [Expr2]/[2016 Median wRVU per Physician Spread Query]![Aug-15]

[ranman256]

instead of doing the division ,paste this custom function into a module so all queries can use it.

press ALT-F11
(on the menu, INSERT, MODULE)
paste the code below
save (module1 is fine)


in a query instead of the :[Expr2]/[2016 Median wRVU per Physician Spread Query]![Aug-15]
in a query field, use: Pct: CalcPct([expr2], [2016 Median wRVU per Physician Spread Query]![Aug-15])

Public Function CalcPct(ByVal pvTop, ByVal pvBtm)
If pvTop = 0 Or pvBtm = 0 Then
   CalcPct = 0
Else
   CalcPct = pvTop / pvBtm
End If

[JoeM]

Check to see if the denominator is zero, i.e:

Expr4: IIF([2016 Median wRVU per Physician Spread Query]![Aug-15]=0,0,[Expr2]/[2016 Median wRVU per Physician Spread Query]![Aug-15])

If Expr2 is zero, it should just return 0, not an error (it is OK to have 0 in the numerator).

IF Expr2 is another calculation that is returning an error, it is best to address that error directly in that calculation. So you will need to check to the calculation of Expr2. Maybe that is also dividing by zero, in which case you would handle that calculation the same was as we handled this one above.

[BP2E]

The formula worked perfectly. Thanks to both of you for your help!