Filtering Data by year

[VSolano]

Hi

As you can see I am new on this. I have a form with monthly data for various year and I want to create a combo box to filter the data by year. I have this event after update on the combo box but it is giving me an error and I do not know why to fix it.
I create a query with every month but the query does not have the date on it. This is why I am using the recordset to pull the field.

Any idea will be greatly appreciate



Private Sub cboYear_AfterUpdate()
Dim DB As Database
Dim RST As Recordset
Set DB = CurrentDb()
Set RST = DB.OpenRecordset("TBMatchAmount")
Dim mthyear As Long
Dim SQL As String
mthyear = RST![MatchMonth]
mthyear = Year(mthyear)

SQL = " SELECT * FROM QRmonthlyReport Where (" & mthyear & ")=" & Me.cboYear & ")"
Me.RecordSource = SQL
Me.Requery

RST.Clone
Set RST = Nothing
Set DB = Nothing
End Sub

 

[arnelgp]

firstly you are opening a recordset on your code,
may i ask why?


secondly your query is missing something.
you filter against a field present in your query,
QRMonthlyreport.


SQL = "Select * From QRMonthlyReport Where Year([FieldInYourQuery]) = " & _
Me.cboYear
Me.RecordSource=SQL
'No need to requery
'Me.Requery

'Not needed
'RST.Clone

RST.Close
Set RST=Nothing
Set DB=Nothing

 

[VSolano]

- I am opening a recordset because I need the date field which is locate on the table no on the query. The query only has the employee name and the monthly amount from January to December.

if I add the date on the report is going to show the employee duplicate. one line for each month

 

[arnelgp]

to make it easier, create another query (Total or Aggregate).
include the Month and Year of the datefield.
therefore it will be easier to get the records you want.

don't limit yourself to one query, trying to get
something that is not present.

making query doesn't bloat your db. so feel
free to make as many with different presentation
of data.

 

[VSolano]

Thanks for your suggestion.

What is your thought on getting the information on a form with just one line for the employee and the amount on each column

 

[arnelgp]

Uuse criisstab query

 

[VSolano]

I want to have the same end result as s crosstab but without using the wizard because I want to be able to filter the date by year or month

 

[arnelgp]

i want to share this with you
if it is not even close to what
you are trying to achieve, just
disregard it.

see the crosstab queries and Form1
that uses them.

see the code behind the form and
the AfterUpdate event of the combobox.