Overview
This lecture covers how to use the LIKE operator with wildcards and the IS NULL operator in Microsoft Access queries to filter data based on specific patterns and missing values.
Using the LIKE Operator and Wildcards
- The LIKE operator filters text fields based on pattern matching, using wildcards.
- The asterisk (*) wildcard represents any number of characters, including none.
- To find records starting with a specific value (e.g., 2019), use '2019*' in the criteria.
- To find records ending with a value (e.g., petrol), use '*petrol' in the criteria.
- To find records containing a value in the middle (e.g., Toyota), use 'Toyota'.
- Wildcards are not case sensitive in Access queries.
- Access automatically adds LIKE and quotes when entering criteria with wildcards.
Examples of Wildcard Use in Queries
- Finding all cities ending with "TX": use '*TX' as the criteria for the City field.
- Finding names starting with "B": use 'B*' for the relevant field.
- Finding surnames containing "an": use 'an' for the Surname field.
- These methods work regardless of position within the field.
Working with NULL Values
- A NULL value means the field is empty (has no value).
- Use "IS NULL" to find records where a field is empty.
- "IS NOT NULL" finds records where a field has any value (not empty).
- "IS NULL" and "IS NOT NULL" are used in the criteria row for queries.
Saving Queries
- Save queries with descriptive names (e.g., "qryNullExample") for future use.
Key Terms & Definitions
- LIKE operator — used to filter records based on pattern matching in text fields.
- Wildcard (*) — symbol replacing any sequence of characters in a query.
- NULL — a database value meaning "no entry" or "missing data".
- IS NULL — a condition that checks if a field is empty.
- IS NOT NULL — a condition that checks if a field is not empty.
Action Items / Next Steps
- Practice using LIKE with wildcards in sample queries.
- Try creating queries filtering for NULL and NOT NULL values.
- Save each query with a clear and descriptive name.