Iff statement won't format currency

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Sammie said:
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?
Click to expand...

Untested, but should work:
CommCheck: IIf([TotLavaCost]=0,"",CCur([ExtendedPrice]-[TotLavaCost]))
 
Sammie said:
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.
Click to expand...


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)
 
This will work in Access 2003 and probably newer versions:




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