Calc'd field won't format?

[LLB]

I have a calc'd percentage field in my query. This is a multi user app and the problem is that is can (and does often) happen that the numerator information for my calc gets entered before the denominator data and therefore the calc'd field shows and as an error.

I attempted to use a simple IIF statement to input a message rater than the error code but now the field will not format to Percent. It worked, I got the message to display and it calc'd the records that had data, it just will not display in percent format. It does let me set the format to percent, it just doesn't display and does not allow the decimal places to be set.

This was the line:
CalcPercent:[Numerator]/[Denominator]

This is the new line:
NewCalcPercent:IIf([Denominator] = 0, "Message", [Numerator]/[ Denominator])

I've tried to search for an answer and I'm sure it is going to be something simple that I've overlooked.

Any thoughts?

 

[Brianwarnock]

I'm pretty certain that it is because you are mixing text and % in the one field, you can get round it for viewing purposes by using
NewCalcPercent:IIf([Denominator] = 0, "Message", [Numerator]*100/[ Denominator]&"%")

but I don't know what the implications are if you were to do further calcs on this.

Brian

 

[LLB]

Thanks Brian,

This works (don't know why I didn't think of it - I'll blame in on "It's Friday") but it doesn't solve the decimal places issue. I still have 13 decimal places showing.

I'm not worried about further calc's, this is the end result.

 

[Brianwarnock]

Missed that point about the decimal places.

NewCalcPercent:IIf([Denominator] = 0, "Message", Round([Numerator]*100/[ Denominator],2) &"%")

should do it.

Brian