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.
If you are using a like criterium (part of the SQL below):
WHERE (ReferenceNumber Like ‘%’ & ? & ‘%’)
and you enter the percent sign in the linked cell, you also get all results back.
Much as what JK said…
Use the ‘like’ operator in your SQL statement.
select CompanyName, City from customers where city like ?
Range examples:
London
B%
B_r%
I cannot get a VBA function to accept a range variable: The following function will not work. I want rng to be a cell range (i.e., A1)
Function ReadCell(rng As Range)
Application.Volatile (True)
ReadCell = Range(“rng”)
End Function
Jeffrey – you use the = Range(“rng”) form where you are referring to a worksheet range with the name “rng”, but in the case of a function you have declared it as a range, so it is already a range object.
Try this:
Application.Volatile (True)
Select Case Rtype
Case 1
ReadCell = rng.Value
Case 2
ReadCell = rng.Address
Case 3
ReadCell = rng.Font.FontStyle
Case 4
ReadCell = rng.Font.Name
Case 5
ReadCell = rng.Height
Case Else
ReadCell = “Rtype is defined for 1 to 5 only”
End Select
End Function
Perfectly works! Thank you for the workaround.
Well done, but really, this should be much easier. For me it’s just easier to comment out the parameter lines in the original query. Not ideal though a simple wildcard would be better. Ms should fix this.