Searching multiple fields from a multi-selection dropdown list

I want to build a query that looks like this:

WHERE (subCatID = 7 OR subCatID2 = 7 OR subCatID = 11 or subCatID2 = 11) AND etc.

The numbers 7 or 11 come from a dropdown list. subCatID and subCatID2 are two fields of the database.

So I want to be able to search two fields with multiple selections from a dropdown list.

I cannot set this up using the DataAssist Interface. Can you tell me how to modify this manually?

The following code does not quite get me there:

'keyword array declarations

'comparison list additions
WAQueryBuilder1_whereClause=BuildFromList("subCatID","subcatID","","=",1,WAQueryBuilder1_wildCard,WAQueryBuilder1_dateSeparator,WAQueryBuilder1_whereClause)
WAQueryBuilder1_whereClause=BuildFromList("subCatID2","subcatID","OR","=",1,WAQueryBuilder1_wildCard,WAQueryBuilder1_dateSeparator,WAQueryBuilder1_whereClause)
WAQueryBuilder1_whereClause=BuildFilterString("dwft","" & cStr( Request.QueryString("minwft") ) & "","AND",">=",1,WAQueryBuilder1_wildCard,WAQueryBuilder1_dateSeparator,WAQueryBuilder1_whereClause)
etc.

It gives me... WHERE ((subCatID = 10) OR (subCatID = 43)) OR ((subCatID2 = 10) OR (subCatID2 = 43)) AND (dwft >= 8) AND etc.

The parentheses are not in the right groupings.

You are not going to be able to edit this manually to get the parenthesis rearranged, if you know the precise query that you want to use you should just create the recordset for it manually.

If you are trying to do a range search, like matching one value in a series of values then you only need to have a single criteria for this. You can select the columns and then the multi select list as the value. This way you will have a range of values that are checked for that criteria.

I tried to create an array WAQueryBuilder1_Array = Array("subCatID1","subCatID2") but it will not do this for a dropdown list with multiple selection possibilities. DataAssist sets it up as a keyword search rather than a list search. It does not seem to work.

Your first example looks correct.

Try using:


WAQueryBuilder1_whereClause=BuildFromList("subCatI D2","subcatID","AND","=",1,WAQueryBuilder1_wildCard ,WAQueryBuilder1_dateSeparator,WAQueryBuilder1_whe reClause)

instead of:


WAQueryBuilder1_whereClause=BuildFromList("subCatI D2","subcatID","OR","=",1,WAQueryBuilder1_wildCard ,WAQueryBuilder1_dateSeparator,WAQueryBuilder1_whe reClause)