Format numbers (Access97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote