Results 1 to 5 of 5
  1. #1
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Placing the decimal place (Access 2000)

    I need to format three columns of numbers in a table before I can export them to a text file.
    The raw data consists of 4 integers and three decimal places. The raw data looks like this 40123. Formatted it should look like 40.123
    Every formatting scenerio I have found seems to only add additional zeros to the end of the value (Ex. 40123.000). I also don't want any rounding to occur.
    Please advise....thanks in advance!


    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Placing the decimal place (Access 2000)

    If I understand correctly, the data contains numbers that have actually been multiplied by 1000. Does the following do what you want (in a query, you could export the query instead of the table)?

    Format([FieldName]/1000,"0.000")

    where FieldName must be replaced by the actual name of a field in your table.

  3. #3
    Star Lounger skip's Avatar
    Join Date
    May 2002
    Location
    Connecticut, USA, Connecticut, USA
    Posts
    50
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Placing the decimal place (Access 2000)

    Hans,
    Thanks so much! That took care of the issue. I formatted the columns and exported the query to a text file.

    Best Regards,

    Skip
    Skip Whitten, MCITP
    http://www.skipwhitten.info

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Portland, Oregon
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Placing the decimal place (Access 2000)

    I also have a number formatting problem... although it seems like it should be easy
    In my form, I have fields to enter interest rates... I would like this field to not show the ".00" if it is "9" .
    It should look like "9" or "9.9" or "12.99", but no zeros. I'm getting "9.00"
    Thanks

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Placing the decimal place (Access 2000)

    You might try using the Format() function with a custom format specification where you use the "#" character as the place-holder on the right side of the decimal. If I correctly interpret the VBA help file, it may give you what you want - to quote:
    <hr>(#) Digit placeholder. Display a digit or nothing. If the expression has a digit in the position where the # appears in the format string, display it; otherwise, display nothing in that position.
    This symbol works like the 0 digit placeholder, except that leading and trailing zeros aren't displayed if the number has the same or fewer digits than there are # characters on either side of the decimal separator in the format expression.<hr>
    Wendell

Posting Permissions

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