Limiting a Report to a Date Range

[A Moloney]

Hi
I have a sub form that pops up asking users to select two
dates (date range: will print all records that are
detailed between these dates) using teh below code. If i
do not enter any dates teh report correctly shows all
records but in the event that one or even two dates are
entered, it advises that the method or data member could
not be found. The following is my code, any advice
greatly appreciated;

Private Sub OK_Click()
Dim strPrintreport As String 'Name of report to open.
Dim strDate As String 'Name of your date field.
Dim strWhere As String 'Where condition for
OpenReport.
Const conDateFormat = "\#dd\/mmm\/yy\#"

strReport = "Printreport"
strField = "Date"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date,
but no start.
strWhere = strDate & " < " & Format
(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date,
but no End.
strWhere = strDate & " > " & Format
(Me.txtStartDate, conDateFormat)
Else 'Both start
and end dates.
strWhere = strDate & " Between " & Format
(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDate,
conDateFormat)
End If
End If

' Debug.Print strWhere 'For
debugging purposes only.
DoCmd.OpenPrintreport strPrintreport,
acViewPreview, , strWhere
End Sub

 

[Allen Browne]

Which line produces the error?

strDate 9s supposed to be the name of your date field, and this field must
be in the RecordSource of the report for the filtering to work. Do you
really have a field named "Date"? That should work in this context, but Date
is a reserved word in VBA (for the system date), so it would be advisable to
rename it.

 

[John Spencer (MVP)]

A guess on my part, but I would be suspicious of the date format you are using
in the where clause. I would try yyyy-mm-dd or mm/dd/yyyy (US Standard) to
ensure that the date is being correctly interpreted.

Const conDateFormat = "\#mm\/dd\/yyyy\#"
or
Const conDateFormat = "\#yyyy\/mm\/dd\#"

 

[Douglas J. Steele]

Access should accept that, John: it's unambiguous.

 

[Marshall Barton]

Access should accept that, John: it's unambiguous.

Only with English Language settings <gdr>

 

[John Spencer (MVP)]

Quibble. What century is YY?

I do agree that it should be unambiguous, I'm just not sure that Access will
recognize that it is a date. But then if Access doesn't recognize it in this
format then it will probably not recognize it as a date in other formats.

? Now what was the OPs question???

 

[Douglas J. Steele]

Valid point, but the OS does take care of that quibble.