Hello, let me start off by saying I am not a programmer by trade but am suffering from the perception that anyone that's good with computers can code like it's a first language. And thus I got conned into setting a fully functional searchable access database, and god knows how lost I'd be in any other database platform.

That said, I'm working on a customer visit record daabase for the sales guys in my company, and for the most part I've been able to borrow enough code posted publicly to put together a basic working on the fly query, but when I attmepted to expand it to utilize searching via a limited date range I start to return an error: 13 type mismatch.

I managed to find some possible answers in this forum from a few years ago and tried the various forms proposed in that thread but I've come to the conclusion I'm bashign my head against a wall at the moment, and I knwo it has to be something painfully obvious to the trained eye, but feels slightly over my head at this point in time.
Private Sub cmdOK2_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strCustomerName1 As String
Dim strCity1 As String
Dim strSalesRep1 As String
Dim dteDateRange As Date
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
Set qdf = db.QueryDefs("qryAtRun")
' Get the values from the combo boxes
If IsNull(Me.cboCustomerName1.Value) Then
strCustomerName1 = " Like '*' "
Else
strCustomerName1 = "='" & Me.cboCustomerName1.Value & "' "
End If
If IsNull(Me.cboCity1.Value) Then
strCity1 = " Like '*' "
Else
strCity1 = "='" & Me.cboCity1.Value & "' "
End If
If IsNull(Me.cboSalesRep1.Value) Then
strSalesRep1 = " Like '*' "
Else
strSalesRep1 = "='" & Me.cboSalesRep1.Value & "' "
End If
If IsNull(Me.txtStartDate1.Value) Then
If IsNull(Me.txtEndDate1.Value) Then
dteDateRange = "= #"
Else
dteDateRange = "<=" & Format$(Me.txtEndDate1.Value, "\#mm\/dd\/yyyy\#")
End If

Else

If IsNull(Me.txtEndDate1.Value) Then
dteDateRange = ">= " & Format$(Me.txtStartDate1.Value, "\#\/mm\/dd\/yyyy\#")
Else
dteDateRange = "Between" & Format$(Me.txtStartDate1.Value, "\#mm\/dd\/yyyy\#") & "And" & Format$(Me.txtEndDate1.Value, "\#mm\/dd\/yyyy\#")
End If
End If

' Build the SQL string
strSQL = "SELECT tblCustomerVisitRecords.* " & _
"FROM tblCustomerVisitRecords " & _
"WHERE tblCustomerVisitRecords.[Customer Name]" & strCustomerName1 & _
"AND tblCustomerVisitRecords.[City]" & strCity1 & _
"AND tblCustomerVisitRecords.[Sales Rep]" & strSalesRep1 & _
"AND tblCustomerVisitRecords.[Date of Visit]" & dteDateRange & _
"ORDER BY tblCustomerVisitRecords.[Customer Name],tblCustomerVisitRecords.[City],tblCustomerVisitRecords.[Sales Rep];"
' Pass the SQL string to the query
Debug.Print strSQL
MsgBox strSQL
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryAtRun") = acObjStateOpen Then
DoCmd.Close acQuery, "qryAtRun"
End If
' Open the query
DoCmd.OpenForm "frmQryAtRunResults"
DoCmd.Close acForm, "frmVisitQuery"
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub

Hello, let me start off by saying I am not a programmer by trade but am suffering from the perception that anyone that's good with computers can code like it's a first language.

Isn't that the truth? Plus you should know hardware, all software, etc, etc.

What is the result of the

Debug.Print strSQL

That will show us the final SQL. Typically a type mismatch occurs when values are surrounded by the wrong thing. Basically you want single quotes around text values, # around date values, and nothing around numeric values.

You are trying (in multiple places, but I'm just showing one) to put data that can't be placed into a date data type:

dteDateRange = "= #"


is no good as it is not a date. Looks like you need to change it to Dim strDateRange As String instead of Dim dteDateRange As Date.

Duh, that's true Bob. It's the high altitude here, that's why I missed that. Yeah, that's the ticket, it's the altitude! :o

Duh, that's true Bob. It's the high altitude here, that's why I missed that. Yeah, that's the ticket, it's the altitude! :o

No worries - we both have those "moments" at times :D (me more than I care to count)

Edit: Thanks I got it working to a large extent. I removed the "=# " and readjusted the date string portion to be the following and it works now as long as I don't pick the same date.
End If
If IsNull(Me.txtStartDate1.Value) Then
If Not IsNull(Me.txtEndDate1.Value) Then
dteDateRange = "<=" & Format$(Me.txtEndDate1.Value, "\#mm\/dd\/yyyy\# ")
End If

Else

If IsNull(Me.txtEndDate1.Value) Then
dteDateRange = ">= " & Format$(Me.txtStartDate1.Value, "\#\/mm\/dd\/yyyy\# ")
Else
dteDateRange = "Between" & Format$(Me.txtStartDate1.Value, "\#mm\/dd\/yyyy\# ") & "And" & Format$(Me.txtEndDate1.Value, "\#mm\/dd\/yyyy\#")
End If
End If

Forgive my ignorance, I have only been programming in Access since version 4.3 (Access 2) and self taught, but what is the logic behing the following


dteDateRange = ">= " & Format$(Me.txtStartDate1.Value, "\#\/mm\/dd\/yyyy\# ")

I can understand the #, / but what is the logic behind the \/:confused:

CodeMaster::cool:

You don't need the \ slashes (which in input masks tell Access to store the literal character which follows). You just need to format as a date:

Fomat(Me.txtStartDate1,"#mm/dd/yyyy#")