When you add a parameter to an external data query, you can point that parameter to a range. Then when the range’s value changes, the query updates based on that parameter. See Parameters for more information.
Someone on the newsgroups asked what they should enter in the cell to show all the records. I tried everything. Emptying the cell showed all the records will no entry in that field. Asterisks (wildcard) showed nothing, but I’m sure it would have shown all the records with an asterisks in that field. The best I could do, eventually, was to use two parameters on the same field.
To demonstrate, this is a query from the Customers table of Northwind.mdb. The query shows the name and city of each customer and the parameter will be on the city field. When you enter a city in the range (to which the parameter points), the query will show all customers from that city. Here’s the query in MSQuery:
I’ve entered two criteria for ‘City’ and each has a parameter; one for greater than or equal to, and one for less than or equal to. The theory is that if both parameters are the same, it will be the same as having one parameterized criterion. In MSQuery, I entered London for each parameter when I refreshed.
I put the query back into Excel and clicked on the Parameter toolbar button on the External Data toolbar. I point the first parameter to cell D1 and check the box to have the query refresh.
I point the second parameter to E1 and don’t check the refresh box.
I enter a formula in E1 as shown below. If D1 is blank, I put a really “large” string in E1. Large meaning that no city name will be greater than this string. I accomplish that be repeating the largest character known to man,
CHAR(255), four times. If D1 has something in it, like Madrid, then E1 has the same value and the effect is the same as one parameter.
When I blank out D1, however, E1 gets this really large string and the query shows all the records whose city is between NULL and ÿÿÿÿ, i.e. all the records.