Formatting data within IIf function

[Guest]

I have a IIf function in a query field that reads like so:

OAR: IIf([Net Operating Income]<>0 And [Sale Price]<>0,[Net Operating
Income]/[Sale Price],"N/A")

[Note: I am learning about the Nz function, but right now this coding,
though more complicated, simulates the same idea of making sure the involved
fields acutally have values...the default values in the table are currently
set to zero]

Here's my question: If possible, when the IIf condition is "true", I'd like
the mathematical calculation to be formatted as a percentage. However, I
think because I have the text "N/A" involved as my "false" value, the
Properties tab for the field is not letting me select any of the number
formats for my field (like Currency, percentage, etc.). I also cannot limit
the decimals returned by the calculation from the Field Properties tab.

My current solution has been to put a Round function on the calculation (2
decimal places) and then multiply the result by 100, then put the "%" sign
behind the figure in the report that runs off the query. Thus, my function
looks like this:

OAR: IIf([Net Operating Income]<>0 And [Sale Price]<>0,Round([Net Operating
Income]/[Sale Price]*100,2),"N/A")

Is there a function I can put on the [Net Operating Income]/[Sale Price]
calculation to have it expressed as a percentage without formatting the
entire field? Also, is there a similar function to express a calculation as
a currency?

I am using Access 2003, mdb file format.

Thanks-
Wil Loftis

 

[JR]

Hil Wil,

How about this?

IIf([Denominator]>0,Format([Numerator]/[Denominator],"Percent"), "N/A")

To format as currency, simply replce the word "Percent" (quotes required)
with "Currency".

JR

 

[Guest]

Perfect! Thanks so much!

Hil Wil,

How about this?

IIf([Denominator]>0,Format([Numerator]/[Denominator],"Percent"), "N/A")

To format as currency, simply replce the word "Percent" (quotes required)
with "Currency".

JR

I have a IIf function in a query field that reads like so:

OAR: IIf([Net Operating Income]<>0 And [Sale Price]<>0,[Net Operating
Income]/[Sale Price],"N/A")

[Note: I am learning about the Nz function, but right now this coding,
though more complicated, simulates the same idea of making sure the
involved
fields acutally have values...the default values in the table are
currently
set to zero]

Here's my question: If possible, when the IIf condition is "true", I'd
like
the mathematical calculation to be formatted as a percentage. However, I
think because I have the text "N/A" involved as my "false" value, the
Properties tab for the field is not letting me select any of the number
formats for my field (like Currency, percentage, etc.). I also cannot
limit
the decimals returned by the calculation from the Field Properties tab.

My current solution has been to put a Round function on the calculation (2
decimal places) and then multiply the result by 100, then put the "%" sign
behind the figure in the report that runs off the query. Thus, my
function
looks like this:

OAR: IIf([Net Operating Income]<>0 And [Sale Price]<>0,Round([Net
Operating
Income]/[Sale Price]*100,2),"N/A")

Is there a function I can put on the [Net Operating Income]/[Sale Price]
calculation to have it expressed as a percentage without formatting the
entire field? Also, is there a similar function to express a calculation
as
a currency?

I am using Access 2003, mdb file format.

Thanks-
Wil Loftis

 

[Guest]

Hi,
I am facing the same problem.
I have two calculated fields in my query, one is:---
Percent Non-Discounted: IIf([No of orders]>0,Format([No of NonDiscounted
orders]/[No of Orders],"Percent"),"N/A")
This one works fine. But I have another field which calculates percentage of
disounted orders. I have set it up as :
Percent Discounted: IIf([No of orders]>0,Format(1-[Percent
Non-Discounted],"Percent"),"N/A")
but this is not working?

Also, in my case, when

Hil Wil,

How about this?

IIf([Denominator]>0,Format([Numerator]/[Denominator],"Percent"), "N/A")

To format as currency, simply replce the word "Percent" (quotes required)
with "Currency".

JR

I have a IIf function in a query field that reads like so:

OAR: IIf([Net Operating Income]<>0 And [Sale Price]<>0,[Net Operating
Income]/[Sale Price],"N/A")

[Note: I am learning about the Nz function, but right now this coding,
though more complicated, simulates the same idea of making sure the
involved
fields acutally have values...the default values in the table are
currently
set to zero]

Here's my question: If possible, when the IIf condition is "true", I'd
like
the mathematical calculation to be formatted as a percentage. However, I
think because I have the text "N/A" involved as my "false" value, the
Properties tab for the field is not letting me select any of the number
formats for my field (like Currency, percentage, etc.). I also cannot
limit
the decimals returned by the calculation from the Field Properties tab.

My current solution has been to put a Round function on the calculation (2
decimal places) and then multiply the result by 100, then put the "%" sign
behind the figure in the report that runs off the query. Thus, my
function
looks like this:

OAR: IIf([Net Operating Income]<>0 And [Sale Price]<>0,Round([Net
Operating
Income]/[Sale Price]*100,2),"N/A")

Is there a function I can put on the [Net Operating Income]/[Sale Price]
calculation to have it expressed as a percentage without formatting the
entire field? Also, is there a similar function to express a calculation
as
a currency?

I am using Access 2003, mdb file format.

Thanks-
Wil Loftis