Solved Filter on Date

[Ole]

Hi
I try to open a report using a filter.
I manage to set the filter correct (DRDate=28-02-2022), but the report won't show me any data. It's blank.
If you're wondering - it's in Danish.

DRDato has the correct format.

I can't get my head around it - need fresh set of eyes on this.

 

[Eugene-LS]

I manage to set the filter correct (DRDate=28-02-2022), but the report won't show me any data. It's blank.

Try in filter:
"(DRDate=" & Format$("28-02-2022", "\#mm\/dd\/yyyy\#") & ")"

 

[Ole]

Thanks!
Now It'll show 28-02-2022 in the report. But when I change the date, it still shows 28-02-2022.
Even if I leave it blank.

This is my code:
Private Sub Kommandoknap7_Click()
On Error GoTo Err_Kommandoknap7_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Dim stLinkCriteria As String
Dim rptMT As String
Dim C1 As Boolean

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
stLinkCriteria = ""

stLinkCriteria = "(DRDato=" & Format$("Me!DRDato", "\#mm\/dd\/yyyy\#") & ")"
rptMT = "Mødetider"

C1 = Me.Report_SetReportFilter(rptMT, stLinkCriteria)

DoCmd.OpenReport rptMT, acViewPreview, , stLinkCriteria, , acWindowNormal

Exit_Kommandoknap7_Click:
Exit Sub

Err_Kommandoknap7_Click:
MsgBox "UPS: Something went wrong"
Resume Exit_Kommandoknap7_Click
End Sub
--------------
Public Function Report_SetReportFilter(pReportName, pFilter)
Dim rpt As Report
DoCmd.OpenReport pReportName, acViewPreview
Set rpt = Reports(pReportName)
rpt.Filter = pFilter
rpt.FilterOn = True
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
End Function
---------------

Does this help?

 

[Eugene-LS]

Does this help?

Yes. As minimum replace that string:

stLinkCriteria = "(DRDato=" & Format$("Me!DRDato", "\#mm\/dd\/yyyy\#") & ")"

to:

stLinkCriteria = "(DRDato=" & Format$(Me!DRDato, "\#mm\/dd\/yyyy\#") & ")"

Or try that code:

Private Sub Kommandoknap7_Click()
Dim stLinkCriteria As String
Dim rptMT As String
Dim C1 As Boolean

On Error GoTo Err_Kommandoknap7_Click
    
    If IsDate(Me!DRDato) = False Then
        MsgBox " ... write as you wish ...", vbExclamation, "Data Error"
        Me!DRDato.SetFocus
        Exit Sub
    End If

    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    Me.Dirty = False
    stLinkCriteria = "(DRDato=" & Format$(Me!DRDato, "\#mm\/dd\/yyyy\#") & ")"
    rptMT = "Modetider"

'As far as I can see, you don't need it! ..............................
    'C1 = Me.Report_SetReportFilter(rptMT, stLinkCriteria) '

    DoCmd.OpenReport rptMT, acViewPreview, , stLinkCriteria, , acWindowNormal

Exit_Kommandoknap7_Click:
    Exit Sub

Err_Kommandoknap7_Click:
    MsgBox "UPS: Something went wrong", vbExclamation, "Error!"
    Err.Clear
    Resume Exit_Kommandoknap7_Click
End Sub

 

[Ole]

Thank you do much. I’ll test it tomorrow.
I really appreciate your effort

 

[Ole]

Yes. As minimum replace that string:

stLinkCriteria = "(DRDato=" & Format$("Me!DRDato", "\#mm\/dd\/yyyy\#") & ")"

to:

stLinkCriteria = "(DRDato=" & Format$(Me!DRDato, "\#mm\/dd\/yyyy\#") & ")"

Or try that code:

Private Sub Kommandoknap7_Click()
Dim stLinkCriteria As String
Dim rptMT As String
Dim C1 As Boolean

On Error GoTo Err_Kommandoknap7_Click
   
    If IsDate(Me!DRDato) = False Then
        MsgBox " ... write as you wish ...", vbExclamation, "Data Error"
        Me!DRDato.SetFocus
        Exit Sub
    End If

    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

    Me.Dirty = False
    stLinkCriteria = "(DRDato=" & Format$(Me!DRDato, "\#mm\/dd\/yyyy\#") & ")"
    rptMT = "Modetider"

'As far as I can see, you don't need it! ..............................
    'C1 = Me.Report_SetReportFilter(rptMT, stLinkCriteria) '

    DoCmd.OpenReport rptMT, acViewPreview, , stLinkCriteria, , acWindowNormal

Exit_Kommandoknap7_Click:
    Exit Sub

Err_Kommandoknap7_Click:
    MsgBox "UPS: Something went wrong", vbExclamation, "Error!"
    Err.Clear
    Resume Exit_Kommandoknap7_Click
End Sub

I've tested it now and it works perfectly :-9
Thank you so much for your help!