Error - data type mismatch in criteria expression

[josephbupe]

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.

 

[arnelgp]

see changes in BuildFilter function.

 

[Eugene-LS]

syntax errors but can't spot any issue that I know of.

You use text as a where condition for numeric fields
For example:

'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]

You use text as a where condition for numeric fields
For example:

'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]

see changes in BuildFilter function.

I really appreciate it

 

[josephbupe]

Just one last thing.

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

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

 

[cheekybuddha]

    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]

    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.

 

[cheekybuddha]

I mis-spelled 'ObjectTags' (I missed the 'b' )

Change:

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

 

[cheekybuddha]

You appear to have a lot of unnecessary code in your cmdSearch_Click() sub.

Maybe adjust to:

  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]

I mis-spelled 'ObjectTags' (I missed the 'b' )

Change:

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

Indeed. I missed that too. Now it works.

Thanks a lot