Iff statement won't format currency


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
 
Ad

Advertisements

N

Neil Sunderland

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?
Untested, but should work:
CommCheck: IIf([TotLavaCost]=0,"",CCur([ExtendedPrice]-[TotLavaCost]))
 
M

Marshall Barton

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.

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)
 
Ad

Advertisements

Joined
May 12, 2014
Messages
1
Reaction score
0
This will work in Access 2003 and probably newer versions:




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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top