I am trying to use one query but with multiple paremeters depending on the cell. For example I have a list of dates in Column A and I want to run the query for each dates shown in column B, so the paramter for B1 is A1, B2 is A2 etc.. How do I do this in Excel?
I suppose you could have a bunch of different queries – one for each cell. I think that would be hard to maintain and probably lead to a huge Excel file. I would use a helper sheet that stored one query and use formula to bring over the data I need.
First, create a query on a sheet that contains all the records for the table you’re interested in. For this example, I’m using the Invoices table from Northwind.mdb (actually mine is called Nwind.mdb, but I don’t know what the difference is). Place the query in A3, or if you already have it in A1, insert a couple of rows above the query. We’ll need them later. If you need help creating this query, see Querying External Data in Excel.
Next, create a new sheet and put your dates in column A. Back on the query sheet, put some formulas in B1 and B2.
These will serve as the parameters for this query. It will limit the rows that are brought in, but there will still be more rows than you need. I needed the
TEXT function because the ODBC driver couldn’t understand Excel’s dates.
Now I’ll make the query a parameter query. I do this in VBA, but you can use Query Manager, Query Editor, or you can edit the SQL statement directly in MSQuery. In the Immediate Window of the VBE, I type
?activecell.QueryTable.CommandText (with a cell in the query selected, or course), and see that the SQL statement is:
To add the parameters, I type this into the Immediate Window:
In the end, the SQL statement should be:
Now when I refresh the query table, Excel will prompt me for parameter values. Just type something that looks like a date and don’t worry about what it returns. If all has gone well, the Parameters button on the external data toolbar will now be enabled. Click it to open the Parameters dialog.
I change Parameter1 to point to cell B1 and Parameter2 to point to cell B2. Now the query is set to return a limited number of rows, but it will contain all the rows you need.
The last step is make the formula on the non-query sheet. With dates in column A, I put this formula in column B to return the first CustomerID for that date.
and I get this: