FILTER PRINT REPORT

[Ashraff]

Why this code does't work. It show no record just a blank table

Private Sub cmdPrint_Click()
    Dim filterCriteria As String
    Dim startDate As Date
    Dim endDate As Date
    Dim currentMonth As Integer
    Dim currentYear As Integer

    On Error GoTo ErrHandler

    ' Get the current month and year
    currentMonth = Month(Date)
    currentYear = Year(Date)

    ' Start date is always 21st of the previous month
    startDate = DateSerial(IIf(currentMonth = 1, currentYear - 1, currentYear), IIf(currentMonth = 1, 12, currentMonth - 1), 21)

    ' End date is always 20th of the current month
    endDate = DateSerial(currentYear, currentMonth, 20)

    ' ? Correct format for Access SQL (yyyy-mm-dd) to avoid misinterpretation
    filterCriteria = "[CheckInTime] >= #" & Format(startDate, "yyyy-mm-dd") & "# AND [CheckInTime] <= #" & Format(endDate, "yyyy-mm-dd") & "#"

    ' Debugging: Show correct filter criteria
    Debug.Print "Filter: " & filterCriteria

    ' ? Show message box in your preferred format (dd/mm/yyyy)
    MsgBox "Filter Range: " & Format(startDate, "dd/mm/yyyy") & " to " & Format(endDate, "dd/mm/yyyy"), vbInformation

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

    Exit Sub

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

 

[Gasman]

Have you walked your code?
What are the results of the Debug.Print statements? at least you have those.

 

[arnelgp]

not tested:

Private Sub cmdPrint_Click()
    Dim filterCriteria As String
    Dim startDate As Date
    Dim endDate As Date
    Dim currentMonth As Integer
    Dim currentYear As Integer

    Dim prevMonth As Date
    
    On Error GoTo ErrHandler

    ' Get the current month and year
    currentMonth = Month(Date)
    currentYear = Year(Date)

    prevMonth = DateAdd("m", -1, Date)
    
    ' Start date is always 21st of the previous month
    startDate = DateSerial(Year(prevMonth), Month(prevMonth), 21)

    ' End date is always 20th of the current month
    endDate = DateSerial(currentYear, currentMonth, 20)

    ' ? Correct format for Access SQL (yyyy-mm-dd) to avoid misinterpretation
    filterCriteria = "DateValue([CheckInTime]) >= #" & Format(startDate, "yyyy-mm-dd") & "# AND DateValue([CheckInTime]) <= #" & Format(endDate, "yyyy-mm-dd") & "#"

    ' Debugging: Show correct filter criteria
    Debug.Print "Filter: " & filterCriteria

    ' ? Show message box in your preferred format (dd/mm/yyyy)
    MsgBox "Filter Range: " & Format(startDate, "dd/mm/yyyy") & " to " & Format(endDate, "dd/mm/yyyy"), vbInformation

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

    Exit Sub

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