multiple criteria / combo box

[britesynth]

I know this is commonly asked, tried looking online but I couldn't get it to work

how do I use 2 combo boxes to filter a query in a form(datasheet)?

for now I'm stuck with this

SELECT qryClients.ClientID, qryClients.FileAs, qryClients.GroupTag, qryClients.SchoolName, qryClients.SchoolAddress, qryClients.Level, qryClients.ClientState
FROM qryClients
WHERE (((qryClients.GroupTag)=[Forms]![frmClientList]![cboGroupTag])) OR ((([Forms]![frmClientList]![cboGroupTag]) Is Null));
(Then I placed a cmd button which will set the property of cboGroupTag to blank = shows up all the records)

I want to add another combo box to filter qryClients.ClientState after qryClients.GroupTag had been filtered
so I can filter each client according to their Group and then filter the clients who are active/inactive


tried one online, something like - WHERE ([field] = cbo or is null AND [field2] = cbo2 or is null)
it didn't work for me, found a video but it's all about vba


thanks in advance
-jake

 

[arnelgp]

on the Where Clause part:

WHERE [qryClients].[GroupTag] = IIF(Trim([Forms]![frmClientList]![cboGroupTag] & "")="",[qryClients].[GroupTag], [Forms]![frmClientList]![cboGroupTag])

this will show all when [cboGroupTag] is blank.

same thing with 2nd combobox:

WHERE [qryClients].[GroupTag] = IIF(Trim([Forms]![frmClientList]![cboGroupTag] & "")="",[qryClients].[GroupTag], [Forms]![frmClientList]![cboGroupTag]) AND [qryClients].[FIELD2]=IIF(Trim([Forms]![frmClientList]![COMBO2] & "")="",[qryClients].[FIELD2], [Forms]![frmClientList]![COMBO2])

just substitute FIELD2 and COMBO2 with actual field and controlname.

 

[britesynth]

thanks sir arnel, it worked!


for now the two combo's aren't cascading so there are values available on combo2 which has no corresponding record for combo1

i tried cascading the combo box, but a message appears something like "type mismatch"

thanks again!
-jake