Date Range Selection

[Guest]

I have entered the following VB to create date sort criteria.


Private Sub Command4_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Const conDateFormat = "\#dd\/mm\/yyyy\#"

strReport = "rptAllRecords"
strField = "PurchaseDate"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then
strWhere = strField & " <= " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then
strWhere = strField & " >= " & Format(Me.txtStartDate, conDateFormat)
Else
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


However when this is run the report generated shows all the records, not
those between the date range selected.

The Report 'Properties' 'Data' 'Filter' shows the following:

(PurchaseDate Between #05/09/2004# And #10/09/2004#) or the equivilent for
other options.

The 'Filter On' criteria = 'yes'

Could anyone help please!

Many thanks

 

[Douglas J. Steele]

Regardless of what your Short Date format has been set to through Regional
Settings, Access will not use dd/mm/yyyy format in queries*. Change

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

to

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

* Okay, so this isn't strictly true. It WILL accept dates in dd/mm/yyyy
format for days of 13 or greater, because it will then realize that it needs
to change. However, your sample dates are not in that range, and will
definitely be treated as 9 May, 2004 through 9 October, 2004.

You might want to take a look at Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
have at http://members.rogers.com/douglas.j.steele/SmartAccess.html