Problem formatting chart data table

[smartin]

I am trying to format the data table of a chart in Access 2010 to show numbers with the thousands separator but no decimal places. In Design View the data table of the chart shows the thousands separator correctly.


Example: 1,271 3,660 1,004 etc.

I have the following expression in the Query Builder in the Chart Row Source property:
Expr1: Sum(Format([SumOfSALES],"#,##0")). This is successfully eliminating the decimal places to show whole number values.

However, when I switch to Print Preview mode, it does not show the thousands separator for some strange reason.
Example: 1271 3660 1004 etc.

For whatever its worth, here is the entire SQL expression in the field mentioned above.

TRANSFORM Sum(Format([SumOfSALES],"#,##0")) AS Expr1
SELECT SummaryForChart.INVENMONTH
FROM SummaryForChart LEFT JOIN MonthLookup ON SummaryForChart.INVENMONTH = MonthLookup.MonthName
GROUP BY MonthLookup.MonthNumber, SummaryForChart.INVENMONTH
ORDER BY MonthLookup.MonthNumber
PIVOT SummaryForChart.INVENYEAR;


I have wasted several hours trying to find a solution but nothing has worked. Does someone have a solution?

[June7]

Did you try formatting the axis number properties? Double click the chart for edit mode, double click the axis line to open the Format dialog. On Number tab, check 'Use 1000 Separator'. Click anywhere on the report to close chart edit.

[smartin]

Yes. I formatted the axis number properties and so the axis numbers are formatted correctly. What is not formatted correctly is the data table.

[June7]

You are showing the chart data table on report? I have never done that. Might not be possible to control this formatting. Want to provide your db for analysis? Follow instructions at bottom of my post.

[smartin]

I can't seem to get a small enough DB to send you but I am attaching a file showing some screenshots to show you what I'm dealing with.

[June7]

Zipped db still too big? 2mb zip allowed.

So actual print of report still does not show commas? Just might be a bug that can't do anything about.

Could try FormatNumber function instead of Format but I am doubtful will make difference.

[smartin]

My challenge was to delete enough data to get a small enough file and yet still retain enough to generate a report. I think I have succeeded. When you open the attached file, click the Make Report button on the Main Screen only. I have deleted enough data that the Import and Calculate buttons will not work. You will notice that I tried the FormatNumber function for the first chart on the report and it does not do anything different than my Format statement was doing. Hope this helps.

[June7]

Nothing I tried worked either. The most I could do was to show or not show decimal. Information I can find says options for formatting the chart data table is very limited. Suggested solution was to use subreport as the data table.

[smartin]

Thanks anyway for checking. I'll just have to be satisfied with it the way it is until Microsoft decides to fix this quirk!