graph date formats - going mental!

[OPMCoordinator]

for the life of me i cant work this one out!

i have a simple little table that has a date and a number that i want to chart.

i have a simple little select query (date range specified in a form) to select my data. no calc required.

i have a simple little line chart on a simple little report that shows the *beep* dates in long format when i want short format.

formatting the axis does not work.
detail section, event, on format,do events trick does not work.

a little history that may have wonked it up;
the table was originally general date format. i have changed it to short.
the form used Now() and Now()-100 for date range for default values. is now 01/01/03 and user type in other. both short date format.

query is short date format.

query behind the graph is also short date format.

every *beep* thing i can find i have set to short date.

it still pulls through the *beepity beeping* general format (date and time - i only want the date!!!!)

please help - i am at my wits end!

mike c

ps i have done some really fancy stuff before in these graphs so this simple little thing is really driving me nuts

 

[ssmithri]

I believe I stumbled across your answer:

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