🔍Mastering Microsoft Access Queries with Wildcards and Criteria
0
10
0
Click to Play Walk-Through
Takeaways:
This video explains how to use wildcards and operators within Microsoft Access queries to perform searches beyond exact matches. It demonstrates using specific dates, ranges of dates with the Between...And operator, finding records with missing data using Is Null, and locating text that contains or starts with certain characters or ranges of letters by employing Like, the asterisk wildcard (*), square brackets ([]), and the Not operator or exclamation point (!). The examples provided illustrate practical applications for finding data like products containing a specific word or employees whose last names fall within a particular alphabetical range.
Database Query Criteria: Wildcards and Operators Study Guide
This study guide reviews key concepts and techniques for filtering data in Microsoft Access queries using wildcards, operators, and functions, based on the provided source material.
1. Basic Criteria:
Queries can be filtered by specifying exact values in the criteria row of the design grid.
This works for various data types, such as specific cities or dates.
2. Wildcards for Pattern Matching (Like Operator):
Wildcards are used when you don't know the exact value you are looking for but know part of it or a pattern it follows.
The Like operator is used in conjunction with wildcards to search for text patterns.
Wildcards are used within double quotes (" ") when searching text fields.
Asterisk (*): Represents any number of characters, including no characters.
Like "chocolate*": Finds text that starts with "chocolate".
Like "*chocolate": Finds text that ends with "chocolate".
Like "*chocolate*": Finds text that contains "chocolate".
Square Brackets ([]): Used to define a range of characters or a set of characters to match.
Like "[a-l]*": Finds text that starts with any character from 'a' to 'l'.
Like "[aeiou]*": Finds text that starts with a vowel.
3. Operators for Ranges and Exclusions:
Between...And: Used to specify a range of values, often used for dates.
Syntax: Between [Value1] And [Value2]
Dates in criteria must be enclosed in pound signs (#).
Comparison Operators (>, <, >=, <=): Used to find values greater than, less than, greater than or equal to, or less than or equal to a specified value.
Example: >Date()-7 (Finds dates within the last 7 days).
Is Null: Used to find records where a specific field has no data (is empty).
Syntax: Is Null
Example: Is Null in the Ship Date field finds orders that haven't shipped.
Not or Exclamation Point (!): Used to exclude results that match a specific pattern or condition.
Not Like "[a-l]*": Finds text that does not start with any character from 'a' to 'l'.
Like "[!a-l]*": Same as above, using the exclamation point inside the square brackets.
4. Using Fields for Criteria Without Showing Them:
A field can be added to the query design grid and used for criteria even if you don't want that field to appear in the final query results.
To do this, uncheck the "Show" checkbox for that field in the design grid.
Quiz
How would you search for all customers whose city starts with the letter "N" using a wildcard?
Explain the difference in function between the asterisk (*) and the square brackets ([]) wildcards when used with the Like operator.
What operator is used to find records where a specific field has no data entered?
If you want to find all orders placed between two specific dates, which operator would you use?
How are dates enclosed when used in query criteria?
Describe how to use a comparison operator and a date function to find records from the last seven days.
What is the purpose of the Like operator in query criteria?
How would you search for products whose name contains the word "sweet" anywhere within the name?
If you wanted to find all employees whose last name does not start with the letter "S", how could you use the Not operator or the exclamation point with a wildcard?
How can you use a field for criteria in a query without displaying that field in the query results?
Answer Key
You would use Like "N*" in the criteria for the City field. The asterisk matches any number of characters after the "N".
The asterisk (*) represents any number of characters, including zero, allowing you to match patterns at the beginning, end, or within text. Square brackets ([]) are used to match a specific set of characters or a range of characters.
The Is Null operator is used to find records where a specific field is empty or has no data.
You would use the Between...And operator to find records within a specified date range.
Dates are enclosed in pound signs (#) when used in query criteria.
You would use a comparison operator like > followed by a date function such as Date()-7. This checks if the date is greater than the date seven days ago.
The Like operator is used in query criteria to search for text patterns when you don't know the exact value you are looking for.
You would use Like "*sweet*" in the criteria for the Product Name field. The asterisks before and after "sweet" indicate that any characters can come before or after the word.
You could use Not Like "S*" or Like "[!S]*" in the criteria for the Last Name field. Both methods exclude names that start with "S".
Add the field to the query design grid, enter your criteria, and then uncheck the "Show" box for that field.
Glossary of Key Terms
Query: A request for data from a database. In Microsoft Access, queries are used to view, change, and analyze data in different ways.
Criteria: Conditions or rules that are used in a query to filter records. Only records that meet the criteria are included in the query results.
Wildcard: Special characters used in query criteria to represent unknown characters or patterns in text values.
Like Operator: An operator used in query criteria to search for values that match a pattern, typically used in conjunction with wildcards.
Asterisk (*): A wildcard character that represents any sequence of characters, including an empty sequence.
Square Brackets ([]): Characters used as part of the Like operator and wildcards to match a single character within a specified set or range of characters.
Between...And: An operator used in query criteria to find values that fall within a specified range (inclusive).
Comparison Operators (>, <, >=, <=): Operators used in query criteria to compare values and retrieve records that are greater than, less than, greater than or equal to, or less than or equal to a specified value.
Date Function (Date()): A built-in function in Access that returns the current system date. It can be used in query criteria, often in conjunction with comparison operators to filter by date ranges relative to the current date.
Is Null: An operator used in query criteria to find records where a specific field contains no data (is empty).
Not: An operator used in query criteria to exclude records that meet a specific condition.
Exclamation Point (!): When used inside square brackets in a Like expression ([!...]), it acts as a "not" operator, matching any single character not within the specified set or range.
Design View: The view in Microsoft Access where you build and modify queries by adding tables, fields, and criteria.
Result Set: The data that is returned by a query after it is run, based on the specified criteria and fields.
Show Checkbox: A checkbox in the query design grid that determines whether a particular field will be displayed in the query results. It can be unchecked to use a field for criteria only.
