How can show graphs in MS Access form

ayazhoda · April 13th, 2007, 11:18 AM

Mdb

robzyc · April 13th, 2007, 12:47 PM

Stange...
Bit clueless as to why the Build would be greyed out...

Is it definately not clickable?

Rob

rgerald · April 13th, 2007, 01:02 PM

Warning: Be very careful using the BETWEEN syntax.

Unless you are absolutely certain that the datatime field contains only the date portion of the datetime (without hours, minutes or seconds), you would be much safer using:

Code:

BETWEEN #01/01/2007# AND #12/31/2007 23:59:59.998#

The syntax you used earlier could lose all the data for the ending date (except for the first instant - 00:00:00.000), if any hours, minutes or seconds are present. The .998 rounds down to the last instant of the date, while .999 often rounds up to the first instant of the next day.

The same caveat applies to

Code:

< #12/31/2007#

or

Code:

<= #12/31/2007#

Rand

ayazhoda · April 16th, 2007, 04:03 AM

yup i am not too much sure but i tried to click on every part of screen but cant find hightlighted Event

robzyc · April 16th, 2007, 04:43 AM

Event!?!

"Build" is not an event, are you using the Access Query Designer to build a query to retrieve the data or are you using a ADO/DAO Recordset in VBA?

Rob

ayazhoda · April 17th, 2007, 10:37 AM

Sorry mistake cant find build highlighted

make it more clear i have graph on form and graph raw source is this query

SELECT (Year([OrderDate])),Sum([TotalValue]) AS [SumOfTotalValue] FROM [Orders] GROUP BY (Year([OrderDate])

I did add combobox so if any one can pick year and graph will display sales for that year

Now i am trying to right click on combobox for build but build is not highlighted

robzyc · April 18th, 2007, 03:31 AM

Ayaz,

While in Form Design Mode, Press "Control" and "G" or press "View" -> "Code".
This will take you to the VBA code window, then select the Combobox Name from the Left drop down list in the middle of the screen, then choose AfterUpdate event in the right.

In the ComboWhatever_AfterUpdate Sub enter the following code:

Code:

GraphName.Requery

(Obviously replace "GraphName" with the name of the graph on your form.
This will requery the graph every time the combo box is changed.

Regards,
Rob

ayazhoda · April 23rd, 2007, 04:14 AM

You are superstar mate
Thanks for such generous help
Regards

robzyc · April 23rd, 2007, 04:27 AM

I take it you got it all worked out?

Always happy to help.

Regards,
Rob

ayazhoda · April 23rd, 2007, 06:28 AM

Hi Rob

I've done this bit but graph doesnt seem updating
but there isnt any error

Private Sub cmbyear_AfterUpdate()

SelectedYear = "01/" + "01/" + (cmbyear.Value)
PreviousYear = "01/" + "01/" + CStr(CInt(cmbyear.Value) - 1)

graphSales.RowSourceType = Query

graphSales.RowSource = "SELECT [SDate],Sum([TotalValue]) AS [SumOfTotalValue] FROM [Orders] where Order.OrderDate Between #" & SelectedYear & "# and #" & PreviousYear & "# Group by(Year([SDate]) * 12 + Month([SDate]) - 1), [SDate])"
graphSales.Requery

End Sub

regards

Ayaz