By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,596 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

using two multiselect boxes to filter a report

P: 7
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
May 14 '08 #1
Share this Question
Share on Google+
4 Replies


patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "AND" & strWhere2, OpenArgs:=strDescrip
  3.  
  4.  
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
May 15 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
Would Example Filtering on a Form help you?
May 16 '08 #3

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "AND" & strWhere2, OpenArgs:=strDescrip
  3.  
  4.  
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
May 20 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. ...IIf(A="asdf"AndB="",1,0)...
This will fail whereas the intended version (using " And ") would work :
Expand|Select|Wrap|Line Numbers
  1. ...IIf(A="asdf" And B="",1,0)...
May 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.