Tuesday 6/19

Embedded Video Player: Sesame Street: One of These Things

In that video, the matches all had something in common, even though they weren't exactly the same.  It was easy, then, to figure out which one didn't match!

This type of thing comes up a lot in Access queries-- for example:

I'm making a directory of employees that will be alphabetized by last name.  Let's say I wanted to get a quick list of all of my employees that have last names starting with S.  But last names that start with S can be of different lengths and have lots of different letters.  So I need a way to describe to access a pattern of similarity-- so I can say "Starts with S".

Enter the Like keyword and wildcards!
The Like keyword in a criterion tells Access that matches will be similar to (or like!) a pattern.  Wildcards are special characters that describe a pattern. 

In our last name example, we can match last names starting with S by entering:

Like "S*"
in the criteria row.  Like tells Access that a pattern is coming up, the S tells access what part should match, and the * (or asterisk character) is a wildcard that means match any number of any character. 

So, Like "S*" will match any of these names: Smith, Szans, Schneider-Wombat, Su, Stellarium, and even just the letter S by itself.

There are several important wildcard characters, but the asterisk * is the most valuable, since it matches any number of any character, including no characters at all.  Also very commonly used is the question mark ? -- which matches exactly one of any character.

Also, whenever you use the asterisk or a question mark in a criterion, Access will automatically enter the Like keyword for you, and will surround your pattern in quotes, so it's even easier to use.

Click on the link above for a demonstration of using wildcards.

A “wildcard” is a special symbol that takes the place of a unknown character or set of characters—it is used in criteria in order to match several items.

In Access queries, wildcards are used with the special operator Like, which indicates that instead of an exact match, you’re looking to match items that fit a certain pattern. The pattern is stated by using wildcards. Like statements always take the form:

Like “<<criteria with wildcards>>”

Where the criteria with wildcards are enclosed in “double-quotes”.

Wildcards:

Wildcards used in Access include:

* (the asterisk): matches any number of characters (including zero characters!)

? (the question mark): matches exactly one character

[a-l] : matches any letter from a to l. (In Access, this is NOT case-sensitive.)

[a,d,l] : matches the letter a, the letter d, or the letter l. (In Access, this is NOT case-sensitive)

Examples:

Criteria Statement	Matches
Like “*”	Anything
Like “B*”	Anything that begins with B, e.g. Bob, Bill, B, Breakfast
Like “*red”	Anything that ends with red, e.g. tired, Fred, red, Winnifred
Like “1/1/*” (for a date field)	Dates on January 1st of any year
Like “C??”	Any three letter word starting with c, e.g. cat, car, cot. Each match MUST have three letters.
Like “[a-c]*”	Anything that begins with A, B, or C, e.g. Acrobat, Bottle, Chant
Like “[d,l]*”	Anything that begins with D or L, e.g. Donut, Lake, Discount

 

Wildcards are also quite useful when writing criteria for dates-- because, as we saw in the section about date functions, there are lots of times when you need to match just a certain Year, Day, or Month of a date.

We've already seen how you can use the Month(), Day() or Year() functions to isolate those parts of a date in one column of a query.

Another method is to use wildcards, specifically the asterisk character.  For example, let's say you have a database famous people and their birthdays, and you want to find out all people who were born on a certain date-- let's say, your own birthday!  In this case, we don't care about the year part, but we do want a match on both month and day.  Assuming your birthday is November 16th, you can write a criterion in the Birthdate column that says:

Like "11/16/*"

to match only dates that match that pattern-- which would be any year on 11/16!

Click on the link above to see a demonstration of using wildcards with date fields.

Now that you've seen some examples of things you can do with wildcards in Access queries, it's time for some practice.  In the online activity that follows, you'll create a few queries.  After you've given it a try, submit your database, and you'll be able to see a detailed walkthrough of the query exercises.

For this Online Activity, download and open the O18Wildcards.docx document listed below, and right-click and save the Birthdays.accdb database to work with.

You'll create a few queries using wildcards in the database.

This activity is due on Tuesday, 6/19 by 11:59pm.

Once you have submitted your activity, you'll be able to see a walkthrough video of me completing it that will appear after this page. (If you don't see it immediately after submitting your work, try logging out of Blackboard and then logging in again-- that should enable the new link).

Click on the link above for a walkthrough of the O18 queries.

Embedded Video Player: Using Wildcards to Find and Filter Data in Access

Now that you've done some practice using wildcards in queries, you're ready for Assignment 20!