Current Location

Tuesday 6/19

Tuesday 6/19 page options


  • One of these things is not like the other...

    One of these things is not like the other... item options
    Hide Details
    Sometimes in Access you aren't able to describe exactly what you want (as in, I would like records where the Price is exactly $10.99, or where the ShoeSize is greater than 10).  Rather, you want to be able able to describe things that are similar to a certain pattern.  You may remember this from your childhood:

    Embedded Video Player: Sesame Street: One of These Things

    Sesame Street: One of These Things
    Duration: (0:30)
    sesamestreet - Added: 7/16/10

  • I want matches like this...

    I want matches like this... item options
    Hide Details
    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.
  • Let's Look at an Example

    Let's Look at an Example item options
    Hide Details
    Attached Files:
    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.
  • Like and Wildcards

    Like and Wildcards item options
    Hide Details
    Attached Files:

    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 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)


    Criteria Statement


    Like “*”


    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 and Dates

    Wildcards and Dates item options
    Hide Details
    Attached Files:
    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.
  • Ready for some practice?

    Ready for some practice? item options
    Hide Details
    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.
  • O18: Wildcards

    Hide Details
    Attached Files:
    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).
  • O18: Walkthrough

    O18: Walkthrough item options
    Hide Details
    Enabled: Adaptive Release
    Attached Files:
    Click on the link above for a walkthrough of the O18 queries.
  • More Resources

    More Resources item options
    Hide Details

    Wildcards are extremely useful in constructing complex queries-- there are lots of great additional resources on this topic available online.  Some good ones are:

    And this video, too!

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

    Using Wildcards to Find and Filter Data in Access
    Duration: (5:35)
    trainsignaloffice - Added: 1/16/12


  • Ready for Assignment 20!

    Ready for Assignment 20! item options
    Hide Details
    Now that you've done some practice using wildcards in queries, you're ready for Assignment 20!

Table of Contents

Minimize Table of ContentsMaximize Table of ContentsMove to the Bottom
  • 1.One of these things is not like the other...
  • 2.I want matches like this...
  • 3.Let's Look at an Example
  • 4.Like and Wildcards
  • 5.Wildcards and Dates
  • 6.Ready for some practice?
  • 7.O18: Wildcards
  • 8.O18: Walkthrough
  • 9.More Resources
  • 10.Ready for Assignment 20!
  • 11.A20: Wildcards - Due 6/20