Date error

[Guest]

Hi, i found this code to find records between two dates. but. there is an
error when i try to find records in this month (10). the format is:
dd/mm/aaaa. note: this program start 2 or 3 weeks ago.

when i tried to look between 20/09/2004 and 30/09/2004 all is ok. but when i
look for this. 02/10/2004 and 05/10/2004 ERROR.

but, if i change the format in the unbound text box to mm/dd/aaaa it works
but only works for the current month (10). then if i look for this:
10/02/2004 and 10/05/2004 all is ok. but this 09/29/2004 and 10/05/2004 it
looks for all records in the report.

help me please
------------------------------------------------------------------------------------------------
Private Sub yes_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#dd\/mm\/yyyy\#"

strReport = "ProductionReport"
strField = "DatePro"

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


'Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
Me.Visible = False
End Su
------------------------------------------------------------------------------------------------

 

[Allen Browne]

The constant is wrong.

Change it to:
Const conDateFormat = "\#mm\/dd\/yyyy\#"

Literal dates in SQL strings must use the American format.

For an explanation, see:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html