Search by keyword intersected with combo box (1 Viewer)

Hi guys! I'm building a search form that has one keyword ("search_box"), 2 combo box ("cc_loc_combo_box" and "cc_deq_combo_box") and one option group ("status_box") search. I'm trying to intersect all the searches. If I intersect the "cc_loc_combo_box" with the keyword search it works, however with the "cc_deq_combo_box" it doesn´t and I'm using the same code... Any help?
In attach I'll send a printscreen showing the query that my subform is based on.

The code is the following :


===============================================
INTERSECT OF "CC_LOC_COMBO_BOX" AND "SEARCH_BOX" (WORKS FINE)
===============================================


ElseIf (Len(Me.cc_deq_combo_box & vbNullString) = 0 And Len(Me.status_box & vbNullString) = 0) Then

SQL = " SELECT LISTA_EQ.CC_DEQ, LISTA_EQ.ID, LISTA_EQ.[No INV], LISTA_EQ.DESCRICAO, LISTA_EQ.MARCA, LISTA_EQ.MODELO, LISTA_EQ.[Num_SERIE / MATRICULA], LOC.[CENTRO CUSTO LOCAL], LOC.[SITUAÇÃO] " _
& " FROM LISTA_EQ INNER JOIN LOC ON LISTA_EQ.ID = LOC.ID " _
& " WHERE (LISTA_EQ.[No INV] LIKE '*" & Me.search_box & "*' AND (LOC.[CENTRO CUSTO LOCAL]) = [Formulários]![SEARCH_EQ]![cc_loc_combo_box]) " _
& " OR (LISTA_EQ.DESCRICAO LIKE '*" & Me.search_box & "*' AND (LOC.[CENTRO CUSTO LOCAL]) = [Formulários]![SEARCH_EQ]![cc_loc_combo_box]) " _
& " OR (LISTA_EQ.MARCA LIKE '*" & Me.search_box & "*' AND (LOC.[CENTRO CUSTO LOCAL]) = [Formulários]![SEARCH_EQ]![cc_loc_combo_box]) " _
& " OR (LISTA_EQ.MODELO LIKE '*" & Me.search_box & "*' AND (LOC.[CENTRO CUSTO LOCAL]) = [Formulários]![SEARCH_EQ]![cc_loc_combo_box]) " _
& " OR (LISTA_EQ.[Num_SERIE / MATRICULA] LIKE '*" & Me.search_box & "*' AND (LOC.[CENTRO CUSTO LOCAL]) = [Formulários]![SEARCH_EQ]![cc_loc_combo_box]) " _
& " ORDER BY LISTA_EQ.[No INV] "


Me.subInfoResults.Form.RecordSource = SQL
Me.subInfoResults.Form.Requery


===============================================
INTERSECT OF "CC_DEQ_COMBO_BOX" AND "SEARCH_BOX"
===============================================


ElseIf (Len(Me.cc_loc_combo_box & vbNullString) = 0 And Len(Me.status_box & vbNullString) = 0) Then

SQL = " SELECT LISTA_EQ.CC_DEQ, LISTA_EQ.ID, LISTA_EQ.[No INV], LISTA_EQ.DESCRICAO, LISTA_EQ.MARCA, LISTA_EQ.MODELO, LISTA_EQ.[Num_SERIE / MATRICULA], LOC.[CENTRO CUSTO LOCAL], LOC.[SITUAÇÃO] " _
& " FROM LISTA_EQ INNER JOIN LOC ON LISTA_EQ.ID = LOC.ID " _
& " WHERE ((LISTA_EQ.CC_DEQ) = [Formulários]![SEARCH_EQ]![cc_deq_combo_box] AND LISTA_EQ.[No INV] LIKE '*" & Me.search_box & "*') " _
& " OR ((LISTA_EQ.CC_DEQ) = [Formulários]![SEARCH_EQ]![cc_deq_combo_box] AND LISTA_EQ.DESCRICAO LIKE '*" & Me.search_box & "*') " _
& " OR ((LISTA_EQ.CC_DEQ) = [Formulários]![SEARCH_EQ]![cc_deq_combo_box] AND LISTA_EQ.MARCA LIKE '*" & Me.search_box & "*') " _
& " OR ((LISTA_EQ.CC_DEQ) = [Formulários]![SEARCH_EQ]![cc_deq_combo_box] AND LISTA_EQ.MODELO LIKE '*" & Me.search_box & "*') " _
& " OR ((LISTA_EQ.CC_DEQ) = [Formulários]![SEARCH_EQ]![cc_deq_combo_box] AND LISTA_EQ.[Num_SERIE / MATRICULA] LIKE '*" & Me.search_box & "*') " _
& " ORDER BY LISTA_EQ.[No INV] "

===============================================

in a continuous form that shows all records,
filter the items after user picks values in the controls, then clicks SEARCH button:

Ranman256 said:

in a continuous form that shows all records,
filter the items after user picks values in the controls, then clicks SEARCH button:

Code:Copy to clipboard

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

Click to expand...

Thanks for the answer! I'm new in Access. How can I adapt that code to my form? I can send you my access file, if you want.

who can help me ?