Error - data type mismatch in criteria expression (1 Viewer)

josephbupe

Registered User.
Local time
Today, 17:18
Joined
Jan 31, 2008
Messages
247
Hello,

I am trying to implement a multiple criteria options to query my database, but I am getting the error "data type mismatch in criteria expression". I have checked through the code for syntax errors but can't spot any issue that I know of.

What I want is any combination of the criteria via text boxes, combos boxes, date range and tags (text separated by commas) to run a search and return the results in the sub-form after clicking the "Search" button.

Maybe someone here can assist to check through the attached sample file.

Regards.
 

Attachments

  • WoADB.accdb
    788 KB · Views: 223

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,169
see changes in BuildFilter function.
 

Attachments

  • WoADB.accdb
    1 MB · Views: 208

Eugene-LS

Registered User.
Local time
Today, 18:18
Joined
Dec 7, 2018
Messages
481
syntax errors but can't spot any issue that I know of.
You use text as a where condition for numeric fields
For example:
Code:
'Wrong:
'    If Me.cmbObjectType & "" <> "" Then
'        varWhere = varWhere & "[ObjectTypeID] = '" & Me.cmbObjectType.Column(1) & "' AND "
'    End If
    
    If Me.cmbObjectType.ListIndex > -1 Then 'Value from list is selected
        varWhere = varWhere & "[ObjectTypeID] = " & Me.cmbObjectType.Column(0) & " AND "
    End If
 

josephbupe

Registered User.
Local time
Today, 17:18
Joined
Jan 31, 2008
Messages
247
You use text as a where condition for numeric fields
For example:
Code:
'Wrong:
'    If Me.cmbObjectType & "" <> "" Then
'        varWhere = varWhere & "[ObjectTypeID] = '" & Me.cmbObjectType.Column(1) & "' AND "
'    End If
   
    If Me.cmbObjectType.ListIndex > -1 Then 'Value from list is selected
        varWhere = varWhere & "[ObjectTypeID] = " & Me.cmbObjectType.Column(0) & " AND "
    End If
Ah yes. Thank you so much
 

josephbupe

Registered User.
Local time
Today, 17:18
Joined
Jan 31, 2008
Messages
247
Just one last thing.

How can i format this part of the code to search multiple keywords in a row?

Code:
    If Me.txtObjectTags <> "" Then
        varWhere = varWhere & "[ObjectTags] Like '*" & Me.txtObjectTags & "*' AND "
    End If
 

cheekybuddha

AWF VIP
Local time
Today, 15:18
Joined
Jul 21, 2014
Messages
2,237
Code:
    Dim vTags As Variant, i As Integer, strWhereTags As String

    If Me.txtObjectTags <> "" Then
        vTags = Split(Me.txtObjectTags, " ")
        For i = 0 To UBound(vTags)
            strWhereTags = strWhereTags & " OR OjectTags LIKE '*" & vTags(i) & "*'"
        Next i
        strWhereTags = Mid(strWhereTags, 5)
        varWhere = varWhere & "(" & strWhereTags & ") AND "
    End If
 

josephbupe

Registered User.
Local time
Today, 17:18
Joined
Jan 31, 2008
Messages
247
Code:
    Dim vTags As Variant, i As Integer, strWhereTags As String

    If Me.txtObjectTags <> "" Then
        vTags = Split(Me.txtObjectTags, " ")
        For i = 0 To UBound(vTags)
            strWhereTags = strWhereTags & " OR OjectTags LIKE '*" & vTags(i) & "*'"
        Next i
        strWhereTags = Mid(strWhereTags, 5)
        varWhere = varWhere & "(" & strWhereTags & ") AND "
    End If
Hi,

Thanks. I am trying to incorporate your code in mine, but I am getting an error: too few parameters, as attached.
 

Attachments

  • error.png
    error.png
    36.8 KB · Views: 196

cheekybuddha

AWF VIP
Local time
Today, 15:18
Joined
Jul 21, 2014
Messages
2,237
I mis-spelled 'ObjectTags' (I missed the 'b' 😖 )

Change:
Code:
' ...
            strWhereTags = strWhereTags & " OR ObjectTags LIKE '*" & vTags(i) & "*'"
' ...
 

cheekybuddha

AWF VIP
Local time
Today, 15:18
Joined
Jul 21, 2014
Messages
2,237
You appear to have a lot of unnecessary code in your cmdSearch_Click() sub.

Maybe adjust to:
Code:
  Dim strFilter As String
 
  strFilter = BuildFilter
  If DCount("*", "qryObjects", Mid(strFilter, 7)) > 0 Then
    Me.frmObjectSearch.frmObjects_Subform.Form.RecordSource = "SELECT * FROM qryObjects " & strFilter
  Else
    'Can't see from the screenshot what you would do here
  End If
 

josephbupe

Registered User.
Local time
Today, 17:18
Joined
Jan 31, 2008
Messages
247
I mis-spelled 'ObjectTags' (I missed the 'b' 😖 )

Change:
Code:
' ...
            strWhereTags = strWhereTags & " OR ObjectTags LIKE '*" & vTags(i) & "*'"
' ...
Indeed. I missed that too. Now it works.

Thanks a lot
 

Users who are viewing this thread

Top Bottom