Blank if zero in a listbox

[Paul Burdett]

Hi Guys

I am populating a listbox from a table using an SQL statement.

One column is currency.

I want to show zero values as blank, not zero. I want to show none blank as
usual currency format.

Is there a format command I can use?

Thanks in advance.

Paul

 

[Al Camp]

Paul,
Formatting the field with...
$ #.00;-$ #.00,""
should do it.
A format can have 4 arguments... seperated by ";"

Section Description...
First The format for positive numbers.
Second The format for negative numbers.
Third The format for zero values.
Fourth The format for Null values.

 

[Paul Burdett]

Hi Al

Many thanks for this.

So how does this look in the SQL?

Format(FieldName, $ #.00;-$ #.00,"")?

This gives me blanks everywhere when I try it. Am I doing something wrong?

Also being software used outside the USA, how would I pick up the local
denomination symbol?

Regards Paul

 

[Al Camp]

Hi Al

Many thanks for this.

So how does this look in the SQL?

Format(FieldName, $ #.00;-$ #.00,"")?

This gives me blanks everywhere when I try it. Am I doing something wrong?

Also being software used outside the USA, how would I pick up the local denomination
symbol?

Regards Paul

 

[Al Camp]

Paul,
Sorry for the blank response.
My suggestion was referring to the Format Property.
The Format Function requires quotes around the format.
Format(FieldName, "$ #.00;-$ #.00, ")?
(the third format argument contains a space)

Regarding the foriegn Currency Units... let me try that one, but repost a new question
if we can't work it out.
Windows has an accessory app called CharacterMap (CharMap.exe), which will display all
the characters in a font. For instance, my Arial has a Pound sign, the Ascii equivalent
of which is 163. So, hold the Alt key down and type 0163 to get that character.
Alt-0163 = £
Format(FieldName, "£ #.00;-£ #.00, ")
or for Francs...
Format(FieldName, "f #.00;-f #.00, ")

You'll have to check your fonts to see if any of them contain the currency symbol/s you
might need. Otherwise, you may have to find a specialty font for foreign currencies. I
would think $ would be pretty much universal though... at least in the business community.

 

[Paul Burdett]

Hi Al

Sorry to take so long in responding.

This works fine so I am very grateful for your help.

The currency thing will do for now using "£" for the UK but I can see that
in Euroland it will cause problems!

Kind regards Paul