using two multiselect boxes to filter a report

[redcali55]

Hello,

Does anyone know what to alter this VBA code (below) so that a report can be filtered using criteria from TWO multiselect boxes? e.g. allowing the user to view products from multiple categories and multiple prices within those categories.

http://allenbrowne.com/ser-50.html




Thanks,
Laura

[patjones]

As a first approximation, I'd say this...

If you look at the two sections of code commented "Loop through the ItemsSelected in the list box" and "Remove trailing comma. Add field name, IN operator, and brackets" - I would probably add another string variable, call it "strWhere2" - and duplicate those two sections of code for the second multi select box, but using strWhere2.

Once strWhere2 is built up, you could then try something like:

 
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "AND" & strWhere2, OpenArgs:=strDescrip
 
 

The WhereCondition argument has to look like a SQL WHERE test, so that's why I'd try using the AND to connect strWhere and strWhere2 to form the whole criteria...

Pat

[NeoPa]

Would Example Filtering on a Form help you?

[redcali55]

Fabulous!

I just had to add "OpenArgs:=strDescrip & "And" & strDescrip2" as well and it worked great!

THANKS!!

As a first approximation, I'd say this...

If you look at the two sections of code commented "Loop through the ItemsSelected in the list box" and "Remove trailing comma. Add field name, IN operator, and brackets" - I would probably add another string variable, call it "strWhere2" - and duplicate those two sections of code for the second multi select box, but using strWhere2.

Once strWhere2 is built up, you could then try something like:

 
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "AND" & strWhere2, OpenArgs:=strDescrip
 
 

The WhereCondition argument has to look like a SQL WHERE test, so that's why I'd try using the AND to connect strWhere and strWhere2 to form the whole criteria...

Pat

[NeoPa]

You're probably fine in your case if it's working, but remember the the word "And" typically requires spaces around it to avoid confusion.

Consider the resulting SQL in this case if the "And" is not surrounded by spaces :

...IIf(A="asdf"AndB="",1,0)...

This will fail whereas the intended version (using " And ") would work :

...IIf(A="asdf" And B="",1,0)...