Formating in IIf statement

[Guest]

I have a query where I'm using an Iif statement where the results will be
either text or a number. So, Format within Properties won't work for me.
Can the formating of the number be written within the Iif statement so it
shows only 2 decimals. If so, how? Here is the statement.

IIf([Standard]is null,"No Standard",[PcsPerProdHrs]/[Standard])

Thank you much

 

[Allen Browne]

Access must treat the field as a number or as text. You cannot have it both
ways.

I suggest that you generate a field that is a number. You can then perform
whatever operations you need (such as summing or averaging), and you can
still print the text in place of the null on a report if you wish.

Do to that, just use something like this in the query:
Expr1: IIf([Standard] = 0, Null, [PcsPerProdHrs]/[Standard])
The expression avoids the division by zero error, and returns Null if either
field is null or of Standard is zero. When you view the query results, you
should see the field right-aligning like a number, not left-aligning as
text.

Then on your report, you can use this in the Control Source of your text
box:
=Nz([Expr1], "No Standard")

 

[Guest]

Perfection. Thank you very much!

Access must treat the field as a number or as text. You cannot have it both
ways.

I suggest that you generate a field that is a number. You can then perform
whatever operations you need (such as summing or averaging), and you can
still print the text in place of the null on a report if you wish.

Do to that, just use something like this in the query:
Expr1: IIf([Standard] = 0, Null, [PcsPerProdHrs]/[Standard])
The expression avoids the division by zero error, and returns Null if either
field is null or of Standard is zero. When you view the query results, you
should see the field right-aligning like a number, not left-aligning as
text.

Then on your report, you can use this in the Control Source of your text
box:
=Nz([Expr1], "No Standard")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query where I'm using an Iif statement where the results will be
either text or a number. So, Format within Properties won't work for me.
Can the formating of the number be written within the Iif statement so it
shows only 2 decimals. If so, how? Here is the statement.

IIf([Standard]is null,"No Standard",[PcsPerProdHrs]/[Standard])

Thank you much