Fred Webb
asked on
Run-time error '3464': Data type mismatch in criteria expression
I have a search form that works fine for text fields but I also want to search on dates but when I enter a date in the search value control it throws the "Data type mismatch in criteria expression" error. I know the the date needs to be wrapped in # but I tried to wrap the the search value with the # in the search field but it still fails. I believe it has to do with my codes search criteria string but I am not sure how to code it for the date field
Private Sub cmdSearch_Click()
On Error GoTo cmdSearch_Click_Error
If Len(Me.cboSearchField & "") = 0 Then
MsgBox "You must select a field to search."
Me.cboSearchField.SetFocus
ElseIf Len(Me.txtSearchString & "") = 0 Then
MsgBox "You must enter a search string."
Me.txtSearchString.SetFocus
Else
If Me.optWild.Value = 1 Then
'Generate search criteria
GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"
Else
GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
End If
'If no results sets focus back to Search Field
'If DCount("*", "tblCountsHistory", GCriteria) = 0 Then
If DCount("*", "qryCountsHist", GCriteria) = 0 Then
MsgBox "No records for the search"
Exit Sub
End If
'Filter frmCountsHistory based on search criteria
Forms!frmCountsHistory.Filter = GCriteria
Forms!frmCountsHistory.FilterOn = True
If Me.optWild.Value = 1 Then
Forms!frmCountsHistory.Caption = "History (" & Me.cboSearchField & " contains '*" & Me.txtSearchString & "*')"
Else
Forms!frmCountsHistory.Caption = "History (" & Me.cboSearchField & " equals '" & Me.txtSearchString & "')"
End If
'Close frmSearch
DoCmd.Close acForm, "frmSearch"
MsgBox "Results have been filtered."
End If
On Error GoTo 0
Exit Sub
cmdSearch_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdSearch_Click of VBA Document Form_frmSearch"
End Sub
Is there a way to determine that the field the user selected in your combo box is a date?
If not, you will need to modify your combo boxes RowSource to provide a datatype as one of the hidden fields in the datatype. You could do that by creating a table with the field names and datatypes, then use the DataType field to determine how you wrap your criteria:
Assuming you would not use wildcards with a date data type.
If Me.optWild.Value = 1 Then
'Generate search criteria
GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"
Else me.cboSearchField.Column(1 ) = "Text" Then
GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
Elseif me.cboSearchField.Column(1 ) = "Date" Then
GCriteria = "[" & Me.cboSearchField & "] = #" & Me.txtSearchString & "#"
Elseif me.cboSearchField.Column(1 ) = "Number" Then
GCriteria = "[" & Me.cboSearchField & "] = " & Me.txtSearchString
End If
You could also use a function to determine the datatype of the field and use that in your If statement
so that your code segment might look like:
Dim strFieldType as string
strFieldType = fnFieldType("YourTableName ", me.cboSearchField)
If Me.optWild.Value = 1 Then
'Generate search criteria
GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"
Else strFieldType = "Text" Then
GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
Elseif strFieldType = "Date" Then
GCriteria = "[" & Me.cboSearchField & "] = #" & Me.txtSearchString & "#"
Elseif strFieldType = "Number" Then
GCriteria = "[" & Me.cboSearchField & "] = " & Me.txtSearchString
End If
If not, you will need to modify your combo boxes RowSource to provide a datatype as one of the hidden fields in the datatype. You could do that by creating a table with the field names and datatypes, then use the DataType field to determine how you wrap your criteria:
Assuming you would not use wildcards with a date data type.
If Me.optWild.Value = 1 Then
'Generate search criteria
GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"
Else me.cboSearchField.Column(1
GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
Elseif me.cboSearchField.Column(1
GCriteria = "[" & Me.cboSearchField & "] = #" & Me.txtSearchString & "#"
Elseif me.cboSearchField.Column(1
GCriteria = "[" & Me.cboSearchField & "] = " & Me.txtSearchString
End If
You could also use a function to determine the datatype of the field and use that in your If statement
Public Function fnFieldType(TableName As String, Fieldname As String) As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
Set fld = tdf.Fields(Fieldname)
Select Case fld.Type
Case dbBigInt, dbInteger, dbLong, dbBoolean, dbDecimal, dbDouble, dbSingle
fnFieldType = "Number"
Case dbChar, dbText, dbMemo
fnFieldType = "Text"
Case dbDate, dbTime
fnFieldType = "Date"
Case Else
fnFieldType = "Other"
End Select
End Function
so that your code segment might look like:
Dim strFieldType as string
strFieldType = fnFieldType("YourTableName
If Me.optWild.Value = 1 Then
'Generate search criteria
GCriteria = "[" & Me.cboSearchField & "] LIKE '*" & Me.txtSearchString & "*'"
Else strFieldType = "Text" Then
GCriteria = "[" & Me.cboSearchField & "] = '" & Me.txtSearchString & "'"
Elseif strFieldType = "Date" Then
GCriteria = "[" & Me.cboSearchField & "] = #" & Me.txtSearchString & "#"
Elseif strFieldType = "Number" Then
GCriteria = "[" & Me.cboSearchField & "] = " & Me.txtSearchString
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fyed
Yes the combo box has a selection CDate with the txtbox containing the date search string. Thanks for your suggestion but it was a little more complicated than needed
Yes the combo box has a selection CDate with the txtbox containing the date search string. Thanks for your suggestion but it was a little more complicated than needed
ASKER
Matt
Your second solution suggestion was just what I needed. Thanks.
Your second solution suggestion was just what I needed. Thanks.
Here is some sample syntax for criteria based on fields of different data types.
'Numeric filter
lngID = Nz(Me![ID])
If lngID <> 0 Then
strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
& "[ID] = " & lngID & ";"
End If
'String filter
strInventoryCode = Nz(Me![InventoryCode])
If strInventoryCode <> "" Then
strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
& "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
End If
'Date range filter from custom database properties
dteFromDate = CDate(GetProperty("FromDate", ""))
dteToDate = CDate(GetProperty("ToDate", ""))
strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
& "[dteDateReceived] Between " & Chr(35) & dteFromDate _
& Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"
'Date range filter from controls
If IsDate(Me![txtFromDate].Value) = True Then
dteFromDate = CDate(Me![txtFromDate].Value)
End If
If IsDate(Me![txtToDate].Value) = True Then
dteToDate = CDate(Me![txtToDate].Value)
End If
strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
& "[dteDateReceived] Between " & Chr(35) & dteFromDate _
& Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"
ASKER
Thanks Helen, I will add these to my Access toolbox of code snippets.
Fred
Fred
Open in new window