Iff statement won't format currency

[Guest]

I am trying to not display CommCheck at all if TotLavaCost = 0, but if
TotLavaCost <>0, then I want to display the result in currency format.

The following statement returns the correct, but unformatted results:
CommCheck: IIf(([TotLavaCost]=0),"",([ExtendedPrice]-[TotLavaCost]))

But the following statement DOES format as currency.
calc: [ExtendedPrice]-[TotLavaCost]
This statement does not exclude records with TotLavaCost=0.

How can I get my unformmatted statement to return results formated as
currency?
Thanks
Sammie

 

[Neil Sunderland]

I am trying to not display CommCheck at all if TotLavaCost = 0, but if
TotLavaCost <>0, then I want to display the result in currency format.

The following statement returns the correct, but unformatted results:
CommCheck: IIf(([TotLavaCost]=0),"",([ExtendedPrice]-[TotLavaCost]))

But the following statement DOES format as currency.
calc: [ExtendedPrice]-[TotLavaCost]
This statement does not exclude records with TotLavaCost=0.

How can I get my unformmatted statement to return results formated as
currency?

Untested, but should work:
CommCheck: IIf([TotLavaCost]=0,"",CCur([ExtendedPrice]-[TotLavaCost]))

 

[Marshall Barton]

I am trying to not display CommCheck at all if TotLavaCost = 0, but if
TotLavaCost <>0, then I want to display the result in currency format.

The following statement returns the correct, but unformatted results:
CommCheck: IIf(([TotLavaCost]=0),"",([ExtendedPrice]-[TotLavaCost]))

But the following statement DOES format as currency.
calc: [ExtendedPrice]-[TotLavaCost]
This statement does not exclude records with TotLavaCost=0.

Since you are returning a (empty) string when it equals
zero, the query thinks the result is text and doesn't try to
apply a numeric type format. Try unsing:

CommCheck: IIf(TotLavaCost=0,Null,ExtendedPrice-TotLavaCost)

 

[DalerWhaler]

This will work in Access 2003 and probably newer versions:




=IIf([TotLavaCost]=0,"",Format(([ExtendedPrice]-[TotLavaCost]), "Currency")