Filter Report by date from Form

[gary9386]

Hi,

I'm pretty new to Access and i'm having trouble trying to filter a
report by date using a form with with two textboxes(txtStart and
txtEnd) and and a command button(cmdOK). I found code online but it
still asks me to enter a parameter value and even when I do i get an
error:

Run-time error '3701':

This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

This error is related to this line of code:

DoCmd.OpenReport strReport, acViewPreview, , strWhere

And here is the rest of the code for the command button:
Private Sub cmdOK_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 = "JOURNAL Query Simulate"
strField = "Date/Time"

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
End Sub


Any help at all would be greatly appreciated.

Thanks.

 

[Carl Rapson]

Hi,

I'm pretty new to Access and i'm having trouble trying to filter a
report by date using a form with with two textboxes(txtStart and
txtEnd) and and a command button(cmdOK). I found code online but it
still asks me to enter a parameter value and even when I do i get an
error:

Run-time error '3701':

This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables.

This error is related to this line of code:

DoCmd.OpenReport strReport, acViewPreview, , strWhere

And here is the rest of the code for the command button:
Private Sub cmdOK_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 = "JOURNAL Query Simulate"
strField = "Date/Time"

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
End Sub


Any help at all would be greatly appreciated.

Thanks.

One thing you might try is putting square brackets [] around your field
name:

strField = "[Date/Time]"

When you print the contents of strWhere with the Debug statement, does the
syntax look correct? Can you paste that text into a Query SQL window and (by
adding the rest of the SELECT statement) retrieve records?


Carl Rapson

 

[gary9386]

The brackets worked perfectly, thanks a lot.