Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44

    Question Unanswered: How to drop the zero on currency under a dollar

    I need to figure out a way to format a price field so that anything that is greater than a dollar, will appear like this (5.95) and anything that is under a dollar will look like this: (.50)

    Is there any way to do this in expression builder or in a function?

    Thank you,

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends where you are using this
    you could do it in a IIF expression in a query
    you coudl do it as a function

    of the two the function call is probably a smarter choice, its extendable, easy to document and debug, but it may well have a performance implication.

  3. #3
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    I am guessing as a function would be better also, but I was unsure how to go about it. It will be used in a query to reformat some currency values. It would need to be a function that has an input value of the corrisponding item number.

    Could you post what you think would be useable. I am out of ideas.

    Thank you!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    immediate window transcript:

    ? format$(.456, ".00")
    .46

    ? format$(1.456, ".00")
    1.46



    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    Where would I put that izyrider? Is that supposed to be a module or a macro or... what?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    wherever you want!

    query:
    SELECT format$(somePrice, ".00") As NoLeadingZero FROM here

    code:
    me.myTextbox = format$(somePrice, ".00")

    control source for a form or report:
    =Format$([somePrice],".00")
    ...watch out - i don't use bound forms but i suspect that the control-source will not be editable in bound-world forms.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Simplest thing to do is to use a format of ".00" in the Format property of the object you want the results in.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Red face

    go 'Trekker
    stupidly obviously naïvely true

    zzzzzzzzzzzzzzzzz why complicate life, izy
    currently using SS 2008R2

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Life is too short to complicate matters
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink euh ....

    Quote Originally Posted by izyrider
    immediate window transcript:

    ? format$(.456, ".00")
    .46

    ? format$(1.456, ".00")
    1.46



    izy
    The thing about this code up here is that if you'll have $1,000.99
    it wil look as 1000.99

    Best way to define currecncy format is : $ #,###,###.00
    So you have your thousand seperator's.
    # is optional, 0 or 9 isn't
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^.^ WHAT did I JUST SAY? ^.^


    lol

    Very true Marvels... but that kinda answers a different problem... and to make it even clearer, the format $#,###.00 is enough. You don't need to go to "millions". Access knows by the ONE COMMA that it is to have a comma every 3 digits. Otherwise your format would have to really be $#,###,###,###,###,###,###,###.00 kinda thing, which is just silly.

    And if you really want to complicate things, the format doesn't take negative, zeros or null display into account, so if you want red negatives and dashes for zero and nulls, you'd need a format of:

    $#,###.00; $#,###.00[Red]; -; -

    But of course, all this is easily discoverable in the help files for the format property, which is where to look for further information on this topic!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •