Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Blank if zero in a listbox

Discussion in 'Information Technology' started by Paul Burdett, Jul 28, 2006.

  1. Paul Burdett

    Paul Burdett
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Al Camp

    Al Camp
    Expand Collapse
    Guest

    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.

    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions


    "Paul Burdett" <paul@burdettp.freeserve.co.uk> wrote in message
    news:OCcHtyIkGHA.1600@TK2MSFTNGP04.phx.gbl...
    > 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
    >
     
  4. Paul Burdett

    Paul Burdett
    Expand Collapse
    Guest

    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" <anon@anon.net> wrote in message
    news:eZgIQNJkGHA.4504@TK2MSFTNGP05.phx.gbl...
    > 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.
    >
    > --
    > hth
    > Al Camp
    > Candia Computer Consulting - Candia NH
    > http://home.comcast.net/~cccsolutions
    >
    >
    > "Paul Burdett" <paul@burdettp.freeserve.co.uk> wrote in message
    > news:OCcHtyIkGHA.1600@TK2MSFTNGP04.phx.gbl...
    >> 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
    >>

    >
    >
     
  5. Al Camp

    Al Camp
    Expand Collapse
    Guest

    "Paul Burdett" <paul@burdettp.freeserve.co.uk> wrote in message
    news:ORlyFvTkGHA.4224@TK2MSFTNGP05.phx.gbl...
    > 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" <anon@anon.net> wrote in message news:eZgIQNJkGHA.4504@TK2MSFTNGP05.phx.gbl...
    >> 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.
    >>
    >> --
    >> hth
    >> Al Camp
    >> Candia Computer Consulting - Candia NH
    >> http://home.comcast.net/~cccsolutions
    >>
    >>
    >> "Paul Burdett" <paul@burdettp.freeserve.co.uk> wrote in message
    >> news:OCcHtyIkGHA.1600@TK2MSFTNGP04.phx.gbl...
    >>> 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
    >>>

    >>
    >>

    >
    >
     
  6. Al Camp

    Al Camp
    Expand Collapse
    Guest

    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.
    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions


    "Paul Burdett" <paul@burdettp.freeserve.co.uk> wrote in message
    news:ORlyFvTkGHA.4224@TK2MSFTNGP05.phx.gbl...
    > 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" <anon@anon.net> wrote in message news:eZgIQNJkGHA.4504@TK2MSFTNGP05.phx.gbl...
    >> 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.
    >>
    >> --
    >> hth
    >> Al Camp
    >> Candia Computer Consulting - Candia NH
    >> http://home.comcast.net/~cccsolutions
    >>
    >>
    >> "Paul Burdett" <paul@burdettp.freeserve.co.uk> wrote in message
    >> news:OCcHtyIkGHA.1600@TK2MSFTNGP04.phx.gbl...
    >>> 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
    >>>

    >>
    >>

    >
    >
     

Share This Page