Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format numbers (Access97)

    I'm trying to format numbers in the data table of a chart on a report.
    The query that the report query is based on points to a table with a units field.
    That fields data type is number and the field size is set to Long Integer.
    When I use this ...Format([tblMAIN_DATA].[UNITS],"#,###") in a query it says that the query contains a field that has an invalid data type.
    I'm using a totals query and trying to sum on that field.
    I want to be able to use the thousands separator on the results.
    Any ideas?
    Thanks,
    Scott

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers (Access97)

    In the query grid, right click on the UNITS field and the Field Property box will display. In the format row, place #,###. This should display the result with the thousands seperator.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers (Access97)

    Thomas,
    That only formats the output on the screen. The actual data is still the same (no thousands separator)
    I double checked that by changing it to a make table query and the "units" field in the new table was unchanged.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers (Access97)

    Sorry, I thought you wanted the results to display in the query. If you want this done in a table, have you tried placing that format in the table design, in the UNITS field property format row?

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers (Access97)

    Thomas,
    Been there, Tried that
    I can get the first query to output the correct data by using:
    UNITS: Format(Sum([tblMAIN_DATA].[UNITS]),"#,###")
    but then the crosstab query for the chart that uses the first query, says the SQL statement contains an invalid data type

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers (Access97)

    Well, I tried this by making a totals query summing on a Long Integer Number field which was formatted as #,###. I didn't use your format statement, I merely used the Field Property box to format the number as I explained in my previous response.
    Then I made a Crosstab query, based on that Totals query and formatted the summed number, again using the Field Property box. It runs without error and with the thousands seperator formatting.

    Have you tried not using your format expression "UNITS: Format(Sum([tblMAIN_DATA].[UNITS]),"#,###")" at all, but just the Field Property boxes? Since it works for me, it seems worth a try ... am I missing something here?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Format numbers (Access97)

    You don't get separators or any other such formatting in the actual data, only in the display format. And if you use Format(), you aren't going to be able to sum that field. Just put the formatting on the control that holds the sum, not on the value it's trying to sum.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format numbers (Access97)

    Charlotte,
    I couldn't find a way to format the cells in the data table of the chart. I guess since it is not an actual control
    I did finally get it to work by using Format(Sum([UNITS]),"#,###") in the crosstab query for the chart. I thought I had tried that before with no luck, but it is working now on 7 different reports.
    Thanks for the help Thomas & Charlotte
    Scott

Posting Permissions

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