Solved

Run-time error '3464': Data type mismatch in criteria expression

Posted on 2011-09-28
7
833 Views
Last Modified: 2013-11-28
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

Open in new window

0
Comment
Question by:skull52
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
A date would use something like this:

          GCriteria = "[" & Me.cboSearchField & "] = #" & Me.txtSearchString & "#"

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
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

Open in new window


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
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
You can also test to see if the search string "looks" like a date:

If IsDate(Me.txtSearchString) Then
    GCriteria = "[" & Me.cboSearchField & "] = #" & Me.txtSearchString & "#"
End If

Open in new window

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:skull52
Comment Utility
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
0
 

Author Comment

by:skull52
Comment Utility
Matt
Your second solution suggestion was just what I needed. Thanks.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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) & ";"

Open in new window

0
 

Author Comment

by:skull52
Comment Utility
Thanks Helen, I will add these to my Access toolbox of code snippets.

Fred
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now