Error 3464 Data type mismatch in criteria expression for between 2 dates look up

  • Thread starter Thread starter shn
  • Start date Start date
S

shn

I am receiving a Runtime Error 3464 Data type mismatch in criteria
expression. This form worked prior and now does not work. Below is the
code. During Debugging the (DoCmd.OpenReport strReport,
acViewPreview, , strWhere) is highlighted. Please Help.



Private Sub btn_UpdatedReportBuild_Click()
Dim strReport As String 'Name of report to Open.
Dim strField As String 'Name of date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptCourses"
strField = "tblcourse.DateChanged"

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

Debug.Print strWhere
DoCmd.OpenReport strReport, acViewPreview, , strWhere
 
Nothing jumps out at me.

Does it fail in all cases, or only one specific combination of
txtupdatedfrom and txtupdatedto being Null or not Null? What's in strWhere
when it fails?
 
It fails in all cases.

tblcourse.DateChanged Between #11/01/2006# And #02/19/2007#

Is in the strWhere when it fails.
 
When using the OpenReport WHERE argument, omit the table name qualifier.
Access already knows what recordset the report is based on. Trying to tell
it a 2nd time cause confusion (i.e., an error).

Access only expects the name of a field in the report's recordset:

DateChanged Between #11/01/2006# And #02/19/2007#


HTH,
 
Back
Top