Passing array as paramter to SQL query

[sumdumgai]

Is it possible to pass an array as a parameter to an SQL query to retrieve data from an Access database? I may have one to many values that need to used as a parameter to then select records from the database. If possible, can someone please provide an example of the query?

Or, is there an alternative way to select records from the database using more than one argument value.

Thanks.

 

[gemma-the-husky]

can you elaborate?

do you have an array that comprises the records you want to select, or something like that?

 

[sumdumgai]

Thanks. I'll be building an array in Excel (or a temporary table in Access) that contains one to many values. These values will be used as arguments to select records from the database which will then be exported to Excel. Example. A field in a customer (Access) table is 'zip' code, which is part of the customer's address. I want to select all of the customers who are in the list of zip codes that are passed, either as an array or as a temporary table, as the parameter. If only one zip code, then I want all customers in that zip code. If more than one zip code, then I want all customers in all those zip codes.

 

[sumdumgai]

Thanks. I'll probably be initiating the arguments (i.e., building the array or string) in Excel. I'll then import that data into Access, building the temp table. I've never passed a table as argument to a query. Can you do that directly or do you have to build the SQL statements using VBA and data in the temp table? If you can do it directly (pass the table as criteria) can you please give me a syntax example?

 

[sumdumgai]

The filtering data (e.g. a list of zip codes) is the user's input to an ad hoc report request. The data that will be filtered, hundreds of thousands of records, already exists in the Access database. I don't know how many zip codes will be passed in any given query. Could be one to hundreds of them, so I can't see a good way to keep the filtering data in Access. Does it make sense now, or can you suggest another way? Thanks.

 

[sumdumgai]

Good ideas. Thanks.