Using IIF when dividing a number by Zero

[djmurphy58]

I have a query that I am using to select all cost codes that have gone over budget. The qry has the following fields:

CostCodeNumber
CostCodeDescription
ToDateBudget
ToDateCost

In addition, I have added the following expr. to the query:

Gain/Loss:[ToDateBudget]-[ToDateCost]

The next expression I need to add is one that calculates the percent over budget (the percent that cost exceeded budget). So the expr would be something like:

% Over: Abs([Gain/Loss]/[ToDateBudget])

The problem comes in when there is ZERO budget and greater than zero cost. This results in "#Error" because I am trying to divide a number by Zero.

I am unfamiliar with the IIF function. How do I modify the above "%Over" expression to display "No Budget" when budget is zero......and then when budget is positive, it needs to display the % Over?

Thanks,
Dennis

 

[PaulBricker]

% Over: IIf([ToDateBudget] = 0,0,Abs([Gain/Loss]/[ToDateBudget]))

That should do it.

Paul

 

[djmurphy58]

Thank you very much Paul. It worked.