Where Condition Problem - Dates

[soco3594]

Hi guys,

I'm new to this forum and relatively new to Access (and VBA) so go easy on me... I've got a button set up on a form to print a report "rptProgressReport" based on a date field in the current record. The code is as follows:

Private Sub Command13_Click()

DoCmd.RunCommand acCmdSaveRecord

Dim strDocName As String
Dim strWhere As String
    strDocName = "rptProgressReport"
    strWhere = "[ReportDate]= # " & Me!ReportDate & " # "
    DoCmd.OpenReport strDocName, acPreview, , strWhere
    
DoCmd.Close acForm, "frmProgressReport", acSaveYes

End Sub

So basically it saves the current record, opens a report based on the "ReportDate" field in the current record, then closes the form. For some reason, the code doesn't like single digit dates i.e. 02-Nov-15 or 08-Dec-15. It works fine for all other dates (e.g. 25-Oct-15 etc.).

Any ideas as to whether my code is lacking, or whether it could be a formatting issue? I have cleared all formatting in my source tables but not having any luck.

Thanks in advance

[June7]

The date structure is not Access/U.S. standard. That might be causing issue. Review http://allenbrowne.com/ser-36.html

[soco3594]

The date structure is not Access/U.S. standard. That might be causing issue. Review http://allenbrowne.com/ser-36.html

Thanks! Got it working. For the record, it was a formatting issue. This is the final code:

Private Sub Command13_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim strDocName As String
Dim strWhere As String
    strDocName = "rptProgressReport"
    strWhere = "[ReportDate]= #" & Format(Me.ReportDate, "mm\/dd\/yyyy") & "#"
        
    DoCmd.OpenReport strDocName, acPreview, , strWhere
    
DoCmd.Close acForm, "frmProgressReport", acSaveYes
End Sub

Thanks again