Results 1 to 10 of 10
  1. #1
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114

    Commas & Decimals Formatting

    I need the report to display a numerical value with both thousand's separator (comma) and decimals and also drop unnecessary zeros at the end of the number.

    Input = 1001.1



    Format Standard = 1,001.10
    Format General = 1001.1

    I want it to read 1,001.1
    (Comma and dropping the zero at the end)

    Decimal is set to Auto. If I increase it the decimal place shows even if it's 0 which I don't want.

    When I try to use the Format function to display both commas and decimals it displays like the Standard format.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    How bout
    Format(field,"0,000.0")

  3. #3
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    Sorry, but using the Format ([Name], "0,000.0") function displays a weird number with a decimal every time (even when there are no numbers to the right of the decimal).

    50 = 0,050.0

    500 = 500.

    If I replace the "0" with a hash, "#" I get the same thing.

    When I use the following formula I get the correct number: Format([Qntty]/[VMDV],"#,###%")

    1160 = 1,160 %

    However, there are numbers to the right of the decimal. Maybe I can have one (numbers to the right of the decimal) or the other (comma) but not both.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    "#,##0.0"

  5. #5
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    Sorry, Ajax, but that doesn't work either. I've tried every combination of the hash and 0 and it always comes up weird. I can get it to work when the number displays as a percent because I only want whole numbers displayed.

    Here's some of the combinations I've tried:

    "#,##0.0" = 1,000.0 (I want the 0 after the decimal to drop because it adds no value)

    "#,##0.#" = 1,000. (The decimal shows up even though there's nothing to the right of it)

    "#,###.#" = 1,000. (The decimal shows up even though there's nothing to the right of it)

    Any other ideas?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so you want a different format, depending on whether there is a decimal place?

    1000=1,000
    1010.1=1,010.1
    56.23=56.23
    999.9=999.9
    847=847

  7. #7
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    Ajax - You nailed it. That's exactly what I'm looking for. I want the format to include both a comma and decimal when necessary.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    only thing I can come up with is

    format(fld,"#,##0" & iif(fld-int(fld)<>0,".##",""))

  9. #9
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    We're getting closer. My only problem is the function keeps throwing a "syntax error". I'm inserting the filed name where you have the "fld". The name of the field is [Qntty], so the function looks like this:

    Format([Qntty],"#,##0" & IIf([Qntty]-(int[Qntty])<>0,".##",""))

    The error message pops up on the (int[Qntty]).

    What am I missing?

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it will - bracket is in the wrong place

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-25-2015, 01:35 PM
  2. Replies: 5
    Last Post: 02-14-2014, 01:50 PM
  3. Replies: 10
    Last Post: 04-17-2013, 04:15 PM
  4. Commas in CSV file
    By cotri in forum Forms
    Replies: 7
    Last Post: 01-28-2010, 03:53 PM
  5. Commas in a Combo
    By DJFayo in forum Forms
    Replies: 0
    Last Post: 10-18-2006, 12:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums