Need To Filter Graph To Only Show Current Month

[vsehijpal]

Just to give a little background to my problem;

I was invoved with building a employment satisfaction survey for work, its going to be rolled out each month. The results are shown on a graph in a real time dashboard that is automatically updated when a survey is submitted, in addition you can aggregate up levels, so from Team Manager, to Department to Business Area. The dashboard also gives an overall average for the level you are viewing and also the number of people who have completed the survey at that level. The dashboard/ database was built in Access and the dashboard results are based on the below VBA.

At the moment we are in the 2nd month of rollout to I need to filter out all records that were submitted prior to the current month. So from whatever area/dept/manager you are viewing you will see only the current month on the graph, and count of people who have submitted the form.

The results graph is built on the following code:


" GraphTotals.RowSource = "SELECT " & TopLevel & ", Format(Avg([Scores2].[Score]),'0.0') AS Recommend " & _
"FROM [Scores2] " & _
"WHERE ([Scores2].[Area_Name] like '" & Area & "') AND Departments.[Department_Name] like '" & Department & "' AND Teams.[Team_Name] like '" & Team & "' " & _
"GROUP BY " & GroupBy"

There is a field in the database that automatically records the date and time when a survey is submitted (date_submitted) and I need to use that to put a filter in the above code. Only problem is I'm really not sure how.

If anyone can offer any suggestions I'd be very grateful.
Vin

 

[Jon K]

"WHERE ([Scores2].[Area_Name] like '" & Area & "') AND Departments.[Department_Name] like '" & Department & "' AND Teams.[Team_Name] like '" & Team & "' AND Format([DateTimeField],'yyyymm')='" & Format(Date(),"yyyymm") & "' " & _


.