Show All with a Range Parameter Query

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:

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.

the parameter dialog for the first parameter

I point the second parameter to E1 and don’t check the refresh box.

the parameter dialog for the second parameter

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.

the result range when Madrid is entered

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.

the result range when nothing is entered

Posted in Uncategorized

6 thoughts on “Show All with a Range Parameter Query

  1. 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.

  2. Much as what JK said…

    Use the ‘like’ operator in your SQL statement.

    select CompanyName, City from customers where city like ?

    Range examples:

  3. 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

  4. 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:

    Function ReadCell(rng As Range, Rtype As Long) As Variant
    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
  5. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.