Multiple field search in combo boxes

[jmsjazz]

I would like to have 5 combo boxes from which users can choose fields to search, e.g.

cbo1 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo2 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo3 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo4 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE
cbo5 - choose YEAR, TYPE, LOCATION, COST, COLOUR, SIZE

criteria are stored in txtbox1, txtbox 2 etc. so the search string could be

WHERE cbo1 = txtbox1 AND cbo2 = txtbox2 etc.

If the user doesn't choose anything for a combo, the search should ignore that field.

Does anyone know a nice way of doing this?

 

[llkhoutx]

Build the criteria programmatically, store it in the forms parameter property, then requery the form.

 

[jmsjazz]

I am not sure what you mean.
I have a command "ApplyFilter" on the form.
I was thinking I could build up the search string, something like

DIM srch1,srch2,srch3,srch4,srchString as String

srch1 - cbo1 & " = " & txtcriteria1 (not sure of the syntax!!)
srch2 - cbo2 & " = " & txtcriteria2
srch3 - cbo3 & " = " & txtcriteria3
srch4 - cbo4 & " = " & txtcriteria4

For x=1 to 4
tmp="srch" & x
IF tmp = NULL THEN
ELSE
srchString = srchString & tmp (best way to include the "AND"??)
ENDIF
Next

DoCmd.RUNSQL (srchString)

If your suggestion is easier, could you give a couple more pointers please?

 

[jmsjazz]

I am still struggling with this; I think I understand what llkhoutx means - I found this post by Robina on Search Combo and Text boxes, answered by BrianWarnock.

When I looked at the parameter query (in Contracts2.ZIP) that was built up from the search form, Design view shows a maximum of 12 "Criteria" rows.
Does the SQL allow more than that, or is there a limit to the number of parameters? I would like to search 12 fields; the SQL statement will get pretty long I guess. I like the simplicity of the search form, but wonder if some code might be easier to write than the SQL?

I went to bed disgruntled with not having come up with a solution, but at 3:50 AM I awoke with a eureka moment. I have tested the code below except for the effective date bit and it allows

1 any combination of combos
2 no combos and keyword
3 any combos and keyword

the keyword can be in any of the fields searched.

Note If you make any changes save in SQL view to demonstrate what happens if you make a change and save in design view I have poste the SQL having removed the like on effective date.

Here is what works, I think

SELECT Contracts.Contract_ID, Contracts.Contract_Status, Contracts.Primary_Vendor_Name, Contracts.Start_Date, Contracts.Contract_Type, Contracts.Contract_Category, Contracts.Description, Contracts.Neg_Contract_Value, Contracts.Initial_Contract_Value, Dept_info.COE, Contracts.Department, Contracts.Dept_Desc_Acct, Contracts.[Multi-Party], Contracts.Original_ext_party, Contracts.[Term Type], Contracts.Effective_Date, Contracts.Initial_term_months, Contracts.[Auto-Renewal_Term], Contracts.Auto_Renewal_Notification_Period, Contracts.Renewed, Contracts.Termination_Convenience, Contracts.Termination_Notice_Req, (DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])) AS [Expiration Date], DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])) AS [Termination Notice], DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])) AS [Renewal Notice]
FROM Dept_info INNER JOIN Contracts ON Dept_info.Description_Acct = Contracts.Dept_Desc_Acct
WHERE (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus] or [Forms]![Search]![CboStatus] is null)
AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend] or [Forms]![Search]![CboVend] is null) 
AND ((Dept_info.COE)=[Forms]![Search]![CboCOE] or [Forms]![Search]![CboCOE] is null)
AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc] or [Forms]![Search]![CboDepDesc] is null )
AND (DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date]))=[Forms]![Search]![txtTermDate] or [Forms]![Search]![txtTermDate] is null )) 
and (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") 
or ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") 
or ((Contracts.Effective_Date) Like "*" & [Forms]![Search]![txtKeyword] & "*"));

Here is what happens if you make a change and save in Design view.

Oops it was to big to post, had to remove a couple of thousand characters

Brian

WHERE (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate])) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Type) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate])) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null)) OR (((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((DateAdd("d",-[Termination_Notice_Req],DateAdd("m",[Contracts]![Initial_term_months],[Contracts]![Effective_Date])))=[Forms]![Search]![txtTermDate]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![CboDepDesc]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Dept_info.COE)=[Forms]![Search]![CboCOE]) AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboVend]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Primary_Vendor_Name)=[Forms]![Search]![CboVend]) AND ((Contracts.Description) Like "*" & [Forms]![Search]![txtKeyword] & "*") AND ((Contracts.Dept_Desc_Acct)=[Forms]![Search]![CboDepDesc]) AND (([Forms]![Search]![CboStatus]) Is Null) AND (([Forms]![Search]![CboCOE]) Is Null) AND (([Forms]![Search]![txtTermDate]) Is Null)) OR (((Contracts.Contract_Status)=[Forms]![Search]![CboStatus]) AND ((Contracts.Description) Like "*" &

 

[catalin.petrut]

I don't understand the combos: on each combo you have the same things to choose from? Maybe it will be more simple to use each combo for a different thing: cbo1 for YEAR, cbo2 for LOCATION and so on. Then you create a query that will have filter criteria for the fields based on the cbo.

 

[jmsjazz]

My form FrmSEARCH has 12 selections - 4 are combos and 8 textboxes. They are named (e.g)
cbotype1
cbotype2
cbotype3
cbotype4
txtsrch1
txtsrch2 etc.

When I click cmd SEARCH, it opens the frmSEARCH, whose data property is set to QRYSEARCH.

QRYSEARCH is a parameter query as shown in CONTRACTS2 - field 1 has criteria [FORMS]![FrmSEARCH]![cbotype1], field 2 has [FORMS]![FrmSEARCH]![cbotype2] etc.

My difficulty is, I do not see enough rows in "Criteria" to allow the complex search - if it is written in SQL will that work? Or is there a simpler way?

I guess what I am looking for is something that gives results similar to the "filter" button that appears at the top of a column in table datasheet view, so you can combine criteria from any selected fields.

 

[jmsjazz]

I finally cracked it by using an array to store the statements and build up the SQL where clause:

e.g.

strSQLHead = "SELECT * FROM tblIncidents "

for x=1 to 12
statement(x) = ""
next x

'DATE
If Len(Me.txtDOBStart & vbNullString) Then
n = n + 1
If Len(Me.txtDOBEnd & vbNullString) Then
statement(1) = " [INCIDENT DATE] Between #" & Me.txtDOBStart & "# AND #" & Me.txtDOBEnd & "#"
Else
statement(1) = " [INCIDENT DATE] >= #" & Me.txtDOBStart & "#"
End If
End If
'COLOUR
If Len(Me.cbocolour & vbNullString) Then
n = n + 1
statement(2) = " [COLOUR] = " & Chr$(39) & Me.cbocolour & Chr$(39)
End If


'OPERATOR
If Len(Me.cbooperator & vbNullString) Then
n = n + 1
statement(3) = " [OPERATOR] = " & Chr$(39) & Me.cbooperator & Chr$(39)
End If

etc. through all cbos and txtboxes

finally:


strSQL = "WHERE "
For x = 1 To 12
If Len(statement(x)) > 0 Then
strSQL = strSQL + statement(x) & " AND"
End If
Next x

strSQL = Left(strSQL, Len(strSQL) - 4)
strSQL = strSQLHead & strSQL

Me.fsubincidents.Form.RecordSource = strSQL

Thanks to those whose code gave me ideas (and code!).