Searching for a Wildcard in a “LIKE” Criteria

In a query, the LIKE operator allows the user to query for a character or group of characters anywhere within a text field.  It does this by matching the field to a string which mixes the character(s) you want to match with wildcard characters like * and ?. Some examples:

• FirstName LIKE "C*"  will return any name that starts with a C (i.e. Carlton, Clark)
• LastName LIKE "*-*"  will return any last name that has a hyphen anywhere in the field (i.e. Flickema-Carlson, Smith-Jones)
• PONum LIKE "C????"   will return and PO number that starts with a C and has exactly 5 characters. (i.e. CSIDF, C24DG)
• PONum LIKE "C##"  returns all values that start with a C, is exactly 3 characters long, and characters 2 and 3 MUST be numbers. (i.e. C45, C16)

So how can I search for a wildcard character itself?  I can simply enclose the wildcard character with brackets [].

For instance, if I wanted to find any value that has an asterisk (*) anywhere in it, I could do this:

• PO LIKE "*[*]*"

If I wanted to find any value that starts with a hash mark(#), I could do this:

• Check LIKE "[#]*"

If I wanted to find any value ending with a question mark (?), I could do this:

• Comment LIKE "*[?]"
  

Showing Query Parameters in a Report

A parameter query is one which asks the user for input.  For instance, suppose I have a query that pulls records for a date range. Suppose further, I want the query to ask me for a Start Date and End Date for the range.  I can create a query like this:

SELECT * FROM MyTable WHERE TheDate BETWEEN [Enter Start Date] AND [Enter End Date]

Running this query will bring up two dialog boxes:

 

Entering the values in the boxes will return the records in that range.

But if I create a Report based on this query, how do I show the selected date range?  After all, reports show information from records in the query’s record source, and Parameter values aren’t included.

There are a couple of possibilities.

Method 1: Read Parameters Directly

The first is to read the parameter directly.  To do that, I just put a text box on my report for each query parameter. In the control source for the text boxes, I put the query parameter preceded by an equal sign.

For instance, in the case of the above query, the control sources for my two text boxes will be:
=[Enter Start Date] and =[Enter End Date]

Alternately, I could have a single text box with your dates concatenated:
=[Enter Start Date] & " - " & [Enter End Date]

I can also fancy it up a bit like this:
=”Date Range: “ & [Enter Start Date] & " to " & [Enter End Date]

Method 2: Read Values Returned

The second option is to read the actual minimum and maximum values from the records in the record source.  I can use the Min and Max functions of the report to do that.  Again, in the Control Source property of a text box:

=Min([Enter Start Date]) & " - " & Max([Enter End Date])

I can, of course, put them in separate text boxes as well.

Comparing The Methods

So what’s the difference between the two methods?

The first method returns the date range requested.  The second method returns the date range actually returned.

Isn’t that the same thing?  Not necessarily.  Suppose I request data from 1/1/2010 to 7/1/2011, but my table only has data starting with 2/1/2011.  The first method will return 1/1/2010 to 7/1/2011 (what I requested).  But the second method will return 2/1/2011 to 7/1/2011 (what’s actually in the record source of the report).