in a query - Too many digits after the decimal point 3

[djmurphy58]

In a query, I have an expression that calculates a percent over budget as follows:

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

Compounding the matter is the fact that sometimes the loss is greater than zero, but the budget is zero. Since I can't divide a number by zero, PaulBricker helped me modify my expression as follows:

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

The problem with this is if the format of the query expression is "percent", then the text "no budget" will not show up, but the calculated percentages will. I want both.

So I changed the format of the expression to "standard" and multiplied the expression by 100. This allows the "no budget" to show up when applicable, AND it allows the calculated value to show - HOWEVER, it shows the calculated value with a ton of digits after the decimal point.

How do I show only 2 digits after the decimal point???? When I create a report based on the query, I try changing it in the field's properties on the report - from "Auto" to "2", but this doesn't work.

Thanks,
Dennis

 

[Golom]

Try this

% Over: IIf([ToDateBudget] = 0,"No Budget",

Format(

Abs([Gain/Loss]/[ToDateBudget])

,"0.00%&quot

)

 

[dhookom]

I would try change the expression to:
% Over: IIf([ToDateBudget] = 0,Null,Abs([Gain/Loss]/[ToDateBudget]))
Then, you can set your Format property of your text box to display null values as "No Budget".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[djmurphy58]

Duane-
Probably a stupid question -

But how do I set the Format property of my text box to display null values as "No Budget"?

Thanks,
Dennis

 

[dhookom]

Not a stupid question. Check Help under "Format Property - Number and Currency Data Types". An example provided is:
$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.