djmurphy58
Technical User
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
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