Filter Form records By Month Year

[maxmaggot]

I have a combobox on my form that gets its information from a query to display the Grouped month and year for which data exists in a table.

I have written some vba code to to figure out the last day (e.g. 31, 28, 30) of the month selected for that year.

I then try to filter my form data based on this.



The problem I am having is that I am only receiving an accumulated total for the number of records. e.g. January 2013 will produce the correct filtered data. February will produce the filtered form with the records for January and February. The code is below:

Private Sub cboFilterFormExpenseMonth_AfterUpdate()
    Dim m_month As Integer
    Dim m_year As Integer
    Dim m_last As Integer
    
    m_month = Mid(Me.cboFilterFormExpenseMonth, 1, 2)
    m_year = Mid(Me.cboFilterFormExpenseMonth, 4, 7)
    m_date = Me.cboFilterFormExpenseMonth
    
    LastDayofMonth = DateSerial(m_year, m_month + 1, 0)
    m_last = Mid(LastDayofMonth, 1, 2)
    
    Me.Filter = "[Payment Date] BETWEEN #01/" & m_date & "# AND #" & m_last & "/" & m_date & "#"
    Me.FilterOn = True
End Sub

I feel the code may be horribly inefficient but it was my last resort to try and get this to work.

Any help would be very much appreciated. Thank you.

[June7]

You want to return only one month data?

Suggest the combobox order the RowSource list by YYYYMM and have this value in a column then use that value as filter criteria. You could show Jan 2012, Feb 2012, etc but the search would use YYYYMM.

Calculate YYYYMM value in query then apply the filter criteria to that constructed field.

[maxmaggot]

Yes, data for just one month that is selected from the combobox dropdown.

So, I change the query that is populating the combobox to display the date in the format YYYYMM in a second field 201302 for Feb 2013, and as Feb 2013 in the first field.
I show the user Jan 2013, Feb 2013 etc in the combobox.
When I run the filter I use the criteria as before looking for dates between, for example, 20130201 and 20130228 (start to end of february).

I think I've misinterpreted your instructions.

[June7]

Almost, don't need the full date nor BETWEEN AND in the filter.

Just:

"YearMo =" & Me.cboFilterFormExpenseMonth

[maxmaggot]

Got it.. Thanks for your help

Also if anyone else is interested, I modified my original code to work also. The problem was that the month and day were getting mixed up. Access needs the date in mm/dd/yyyy format and I was filtering on dd/mm/yyyy. The updated code is below:

Private Sub cboFilterFormExpenseMonth_AfterUpdate()
    
   
    Dim m_month As Long
    Dim m_year As Long
    Dim m_last As Long
   
    
    m_year = Mid(Me.cboFilterFormExpenseMonth.Column(1), 1, 4)
    m_month = Mid(Me.cboFilterFormExpenseMonth.Column(1), 5, 2)
    m_date = Me.cboFilterFormExpenseMonth.Column(1)
    
    LastDayofMonth = DateSerial(m_year, m_month + 1, 0)
    m_last = Mid(LastDayofMonth, 1, 2)
    
    m_start = "#" & m_month & "/01/" & m_year & "#"
    m_finish = "#" & m_month & "/" & m_last & "/" & m_year & "#"


    Me.Filter = "[Payment Date] BETWEEN " & m_start & " AND " & m_finish
    Me.FilterOn = True
    
End Sub