Filter Records (1 Viewer)

[Ashraff]

Hello!

Can someone help me with this code:

i want to create a filter for my report, when click print it will filter date 21 previous month to 20 current month (example: 21 january - 20 february). The start date in reports should be 21 previous month to current.
Option Compare Database

' Handles the month filter change
Private Sub cboMonthFilter_AfterUpdate()
Dim selectedMonth As Integer
Dim filterCriteria As String

On Error GoTo ErrHandler

' Check if "All" is selected and clear only the month filter
If Me.cboMonthFilter = "All" Then
ApplyFilters
MsgBox "Month filter cleared. Showing all records for EmpNo: " & Me.EmpNo, vbInformation
Exit Sub
End If

' Check if a valid month is selected
If IsNull(Me.cboMonthFilter) Or Me.cboMonthFilter = "" Then
Me.FilterOn = False
MsgBox "Filter cleared.", vbInformation
Exit Sub
End If

' Convert the selected month name to a month number
selectedMonth = MonthNameToNumber(Me.cboMonthFilter.Value)

' Apply filters
ApplyFilters

Exit Sub

ErrHandler:
MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub

' Handles the year filter change
Private Sub cboYearFilter_AfterUpdate()
On Error Resume Next
ApplyFilters
End Sub

Private Sub ApplyFilters()
Dim filterCriteria As String
Dim selectedMonth As Integer
Dim selectedYear As String
Dim recordCount As Integer
Dim empNumber As String

' Store the current EmpNo before applying the filter
empNumber = Nz(Me.EmpNo, "")

' Ensure EmpNo is not empty
If empNumber = "" Then
MsgBox "No Employee Number found!", vbExclamation
Exit Sub
End If

' Start with a base filter for EmpNo
filterCriteria = "EmpNo = '" & empNumber & "' AND Not IsNull([CheckInTime]) AND Not IsNull([CheckOutTime])"

' Handle year filter
If Not IsNull(Me.cboYearFilter) And Me.cboYearFilter <> "All" Then
filterCriteria = filterCriteria & " AND Year([Date]) = " & Me.cboYearFilter
End If

' Handle month filter
If Not IsNull(Me.cboMonthFilter) And Me.cboMonthFilter <> "All" Then
selectedMonth = MonthNameToNumber(Me.cboMonthFilter.Value)
filterCriteria = filterCriteria & " AND Month([Date]) = " & selectedMonth
End If

' Apply the filter
Me.Filter = filterCriteria
Me.FilterOn = True

' Check the number of records
recordCount = Me.Recordset.recordCount

' If no records are found, keep only the EmpNo filter and prevent empty dataset issue
If recordCount = 0 Then
MsgBox "There are no overtime records.", vbExclamation, "No Records Found"

' Restore only the EmpNo filter
Me.Filter = "EmpNo = '" & empNumber & "' AND Not IsNull([CheckInTime]) AND Not IsNull([CheckOutTime])"
Me.FilterOn = True

' Move to the first record to ensure form does not appear empty
If Not Me.Recordset.EOF Then
Me.Recordset.MoveFirst
End If
End If
End Sub


' Convert month name to month number
Private Function MonthNameToNumber(monthName As String) As Integer
Select Case monthName
Case "January": MonthNameToNumber = 1
Case "February": MonthNameToNumber = 2
Case "March": MonthNameToNumber = 3
Case "April": MonthNameToNumber = 4
Case "May": MonthNameToNumber = 5
Case "June": MonthNameToNumber = 6
Case "July": MonthNameToNumber = 7
Case "August": MonthNameToNumber = 8
Case "September": MonthNameToNumber = 9
Case "October": MonthNameToNumber = 10
Case "November": MonthNameToNumber = 11
Case "December": MonthNameToNumber = 12
Case Else: MonthNameToNumber = 0
End Select
End Function

Private Sub cmdPrint_Click()
Dim filterCriteria As String
Dim startDate As Date
Dim endDate As Date
Dim currentDate As Date
Dim prevMonth As Integer
Dim prevYear As Integer

On Error GoTo ErrHandler

' Get the current date
currentDate = Date

' Determine start and end date for filtering
If Day(currentDate) >= 21 Then
' If the current day is 21 or later, set startDate to the 21st of the previous month
startDate = DateSerial(Year(currentDate), Month(currentDate) - 1, 21)
endDate = DateSerial(Year(currentDate), Month(currentDate), 20)
Else
' If the current day is before the 21st, set startDate to the 21st of two months ago
prevMonth = Month(currentDate) - 2
prevYear = Year(currentDate)
If prevMonth <= 0 Then
prevMonth = prevMonth + 12
prevYear = prevYear - 1
End If
startDate = DateSerial(prevYear, prevMonth, 21)
endDate = DateSerial(Year(currentDate), Month(currentDate) - 1, 20)
End If

' Construct filter criteria with properly formatted dates
filterCriteria = "[CheckInTime] >= #" & Format(startDate, "mm/dd/yyyy") & "# AND [CheckInTime] <= #" & Format(endDate, "mm/dd/yyyy") & "#"

' Open the "OvertimeRecords" report with the current filter
DoCmd.OpenReport "OvertimeRecords", acViewPreview, , filterCriteria

Exit Sub

ErrHandler:
MsgBox "An error occurred while trying to print: " & Err.Description, vbCritical
End Sub

 

[June7]

Please edit your post to place code between CODE tags. This will retain formatting and make it easier to read. Click the </> button on post edit toolbar.

Or you can provide db file for analysis. Follow instructions at bottom of my post.

Explain issue with code - error message, wrong result, nothing happens? Have you done any debugging?