Passing multiple criteria to query in access via VBA code

Question

I am working on a Access database that I inherited from the person working on it before me. I am novice at this and learning as I go along.

Background: I am working with a database of experimental data. I am trying to create a search and export form which will allow the the user to search for a particular data set and then export that to an excel sheet. It will also let the user filter the search so as to write only specific values into the excel file.

My data base has 2 main sub tables differentiating the kind of experiments. I use a list box that allows the user to choose which table he/she wants to search.

based on this selection two additional listboxes get populated with the relevant options

the options are "Author Name" and a "Number ID" Upto this point everything works if table 1 is selected one set of author names and number ids are populated in the list boxes and the same is true if table 2 is selected.

the author name and also the number id list boxes are simple multiselect.

I loop over the list box selection to get all the values selected

I then create the SELECT and IN statement.

This works like a charm for the number id (when i execute with only 1 list box).

However I get an error if i try to create a query statement as follows:

SELECT * FROM tbl1 [Number_ID] IN (a,b,...) AND 
SELECT * FROM tbl1 [AUTHOR_NAME] IN ("xyz", "abc",....)

where Number_ID is an Integer and Author_Name is text

Thanks a lot Hans :) somehow the WHERE clause got edited out of my original question. However removing the second SELECT .... FROM clause fixed the issue :) — Ashwin_P
Hopefully I Answered and posted correctly. Thanks once again. — Ashwin_P

Ashwin_P Ashwin_P · Accepted Answer · 2015-08-28T18:10:15

Solution:

strSQL = "SELECT * FROM tbl1 WHERE "

strWhere = "[Number_ID] IN (1, 2, 3, ...)"
strSQL = strSQL & strWhere

strSQL1 = " AND "
strWhere1 = "[Author_Name] IN ("xyz", "abc", ...)"

strSQL = strSQL & strSQL1 & strWhere1

This generates a query string as follows

"SELECT * FROM tbl1 WHERE [Number_ID] IN (1, 2, 3, ...) AND [Author_Name] IN 

("xyz", "abc", ...)"

which can be passed to your qryDef variable to create a query.

Passing multiple criteria to query in access via VBA code

1 Answers