Graphing Data Table Question

[JCollins]

I have a line chart which is working fine, but when I show the Data table underneath the chart, all of the values show up as regular numbers. I want them to show up as currency.

One would think that you could format the data table cells itself as a currency and that would work...Since the source for the graph is a query where the fields are "sums"..it does not want to work.

I have even tried to modify the query to show TotalSales: ccur(Sum([tblname].[Sales]))...Again, this looks fine in the query, but it loses the currency formatting in the graph...

Does anyone know how to solve this?

 

[simongallop]

Change it to Ccur in the rowsource query of the graph. Then view that query, copy the result and paste into the sample data of the graph. The data should show up in the grid in currency format

 

[Dachande11]

Hi,

I was just wondering if this solution worked for you as I do not quite understand what Harry is saying.

Thanks

Mark

 

[ssmithri]

I believe I have the answer to this one... I was having the same issue and ran across this:

http://ourworld.compuserve.com/homepages/attac-cg/ARptTip.htm#GRAPHGEN

Some of the most common questions related to Access reports involve problems with Microsoft Graph or formatting Graph presentations. Here's a few common issues and how to solve them with Visual Basic.

1) Problem: When the report is previewed or printed, Graph doesn't display the proper data from its record source, instead it displays either the data from a prior record or the sample data in the Graph data sheet:

Solution:

Add the following Visual Basic code to the On Print event of the section of the report that contains the Graph object.


On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
DoEvents
Set objGraph = Nothing
2) The data in the datasheet and sample data shown in Graph in design view is not the data from the record source specified, rather it is some default sample data; so its hard to properly design the graph.
Solution:

Modify the code above adding the following 5th line shown below, preview the report and save it.


On Error Resume Next
Dim objGraph As Object
Set objGraph = Me!TheNameOfYourGraph.Object
objGraph.Refresh
'This will update the data sheet
objGraph.Application.Update
DoEvents
Set objGraph = Nothing

3) Problem: The data table displayed on your Graph will not display the number format you've specified in the Graph's record source (e.g. display only one decimal place by using Format([YourField], "#.0") in the query:

Solution:


Graph sets this formatting in the data sheet view when graph is in design mode; right click on the column that represents the series of data displayed (e.g. column A is series 1, B Series 2 etc.) and choose the "Number format" option.

If Graph still doesn't hold the formatting you desire after setting the format in the datasheet, add code like the following to the On Print event of the same section of the report that contains your Graph:

On Error Resume Next
Dim objGraph As Object
Dim objDS as Object
Set objGraph = Me!TheNameOfYourGraph.Object
Set objDS = objGraph.Application.DataSheet
'Singe decimal place, 200 data points
'Format is the same as Excel VBA
objDS.Range("A1:A200").NumberFormat = "#.0%"
objGraph.Refresh
DoEvents
Set objGraph = Nothing