Introduction

Welcome! Query Criteria, Wildcards & Params

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Access Beginner Level 5. In this course we will focus on query criteria, including using multiple conditions with AND and OR, inequalities, searching between values and dates, handling null values, using wildcards and the LIKE keyword, and working with parameter queries where Access can prompt you for input. We will reference concepts from previous beginner levels and clarify the difference between beginner, expert, advanced, and developer classes. I will encourage you to follow along and make use of the student forums throughout the lessons. Finally, we will outline the specific topics covered in each lesson.

Navigation

Prev: B5
Next: Lesson 01

Keywords

Access Beginner, query criteria, multiple conditions, AND OR conditions, parameter queries, wildcards, LIKE keyword, NOT keyword, field properties, null values, IS NULL, IS NOT NULL, between two values, date criteria, show hide fields, wildcard searches,

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access 2010 Beginner Level 5, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class will focus mainly on query criteria, using multiple conditions, working with different AND and OR conditions, inequalities, and finding records between two values, including dates. We will also cover working with null values, learn about wildcards and the LIKE keyword.

Then we will learn about parameter queries, where Access can prompt us for a value when we run the query, instead of having to make multiple queries to look for different types of values. For example, we will not need to make 50 different queries for each state. We can just have Access ask us for the state that we are looking for.

This class follows Access 2010 Beginner Level 4 where we learned about field properties, database maintenance, and the find and replace function. In today's class, we will be relying on a lot of the knowledge that we learned in Level 1, building queries, and Levels 3 and 4 when we work with field properties.

In this class, we will be using Access 2010. If you are using 2007, you should not have any problems following along as the two versions are almost identical. If you are using Access 2003, you should not have any problems following along with this class. The material covered today is just about the same in Access 2003. The menus are a little different, and the ribbon has obviously been added in 2007 and 2010, but the concepts covered today are all pretty much the same.

I encourage you to actually build the database that I build in class, but if you would like to download a copy of my database file, you can find it here: accesslearningzone.com/databases.

Today, for example, we are focusing on queries and I have already built a customer table - we built it in Access 1 - and if you do not want to have to retype all that data in, and you do not have a copy of the database yourself that you have already built, feel free to download it from my website.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer.

My beginner courses are for novice users who have little or no experience with Microsoft Access. They are designed to give you an overview of the basic features of Access and cover just what you need to know to be productive.

The expert classes are designed for more experienced users who are already comfortable with Access. Expert classes go into a lot more depth about each topic than the beginner classes do, and they will cover more functions, features, tips, and techniques for power users.

Once you have mastered the expert classes, move up to the advanced lessons. You will learn how to work with macros, automation, and many more advanced features that really add enhanced functionality and professionalism to your databases.

Finally, my developer level courses are designed to teach you how to program in Visual Basic for Microsoft Access. This will allow you to create the most advanced databases possible and unlock the true potential of Microsoft Access.

Each series is broken down into different numbered levels, starting with Level One. Each subsequent level teaches you new and different topics in Microsoft Access, building on the lessons learned in the previous classes. Once you have finished all the beginner classes, move up to the expert series, then the advanced, and finally, the developer lessons.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include: building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here, you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students may have made.

I encourage you to read through these questions and answers as you start each lesson, and feel free to post your own questions and comments as well.

If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from this class.

If you get stuck or do not understand something, watch the video again from the beginning, or tell me what is wrong in the student forum. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now let's take a closer look at exactly what is covered in today's class.

In Lesson 1, we are going to learn more about query criteria. We are going to learn about multiple criteria, and using different AND and OR conditions to get exactly the data that you want to see.

In Lesson 2, we are continuing with query criteria. We will learn about the NOT keyword, we will learn how to show or hide fields from the query results, and we will learn about working with dates and times as our query criteria.

In Lesson 3, we are continuing on with query criteria. We are going to talk about the IS NULL and IS NOT NULL keywords, the LIKE keyword, and performing wildcard searches.

In Lesson 4, we are going to learn about something very powerful called a parameter query. This is where you can specify a value when the query runs.

In Lesson 5, I will answer questions from students.

Thank you.

Intro

Quiz

Q1. What is the main focus of Microsoft Access 2010 Beginner Level 5?
A. Report formatting
B. Query criteria and parameter queries
C. Creating tables
D. Table relationships

Q2. Which function allows Access to prompt the user for a value when the query runs, eliminating the need for multiple queries for different values?
A. Crosstab query
B. Parameter query
C. Make Table query
D. Append query

Q3. What types of query criteria will be covered in this class?
A. AND and OR conditions, inequalities, and working with null values
B. Exporting and importing data only
C. Designing custom forms
D. Creating macros

Q4. If you want to find all records between two dates in a query, which topic from this class would help you?
A. Exporting tables
B. Query criteria with date ranges
C. Sharing databases online
D. Printing reports

Q5. What is the LIKE keyword used for in Access queries?
A. Sorting records alphabetically
B. Performing wildcard searches
C. Copying records from one table to another
D. Calculating totals

Q6. What is the recommended way to use this course for maximum benefit?
A. Watch part of the lesson and then do a different project
B. Skip watching and just read the transcript
C. Watch the lesson once fully, then replay and follow along building the sample database
D. Memorize all the menu options

Q7. If you are using Access 2003, what should you expect when following along with this class?
A. You will not be able to follow the lessons at all
B. All features are completely different
C. Menus are different, but the query concepts are the same
D. The database files are not compatible

Q8. What kinds of users are the beginner courses designed for?
A. People with advanced knowledge of Access
B. People with no experience or novice users
C. Professional Access developers
D. Users focused on web databases only

Q9. What should you do if you do not want to type in all the sample data?
A. Wait for a printed copy to arrive
B. Download the sample database file from the website
C. Skip the lessons that require data
D. Only watch the video and take notes

Q10. In which lesson will parameter queries be taught?
A. Lesson 2
B. Lesson 1
C. Lesson 3
D. Lesson 4

Q11. Where can you post questions related to the lessons?
A. Only by email
B. AccessLearningZone.com student forums
C. Through postal mail
D. There are no forums available

Q12. What should you do if you get stuck or do not understand something in a lesson?
A. Ignore the problem and continue
B. Watch the video again or ask for help in the forums
C. Restart your computer
D. Skip to the next course

Answers: 1-B; 2-B; 3-A; 4-B; 5-B; 6-C; 7-C; 8-B; 9-B; 10-D; 11-B; 12-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone focuses on Microsoft Access 2010 Beginner Level 5. My name is Richard Rost and I'll be your instructor for this course.

In this class, our main concentration is on query criteria. You'll learn how to apply multiple conditions to your queries, work with both AND and OR logic, use inequalities, and locate records that fall between two values, including specific dates. We'll also discuss how to handle null values, utilize wildcards, and make use of the LIKE keyword.

Another topic for today is parameter queries. This feature allows Access to prompt you for a value when you run your query, so instead of creating dozens of nearly identical queries for different values, you can just enter what you need when asked. For example, if you want to filter customers by state, you won't need to build fifty different queries - you just tell Access which state you're interested in when the query runs.

This lesson builds on material covered in Access 2010 Beginner Level 4, where we looked at field properties, database maintenance, and find and replace functions. We will also rely on skills from Level 1 relating to creating queries, and from Levels 3 and 4 when it comes to field properties.

We'll be using Access 2010, but if you are working with Access 2007, you should have no trouble following along, as the versions are nearly identical. Access 2003 users will also be able to keep up, since the underlying features are much the same. The menu structure is a bit different and 2007 and 2010 introduced the ribbon, but the core concepts are consistent.

If you'd like to follow along with exactly the same data that I use in class, you have the option to download my sample database from accesslearningzone.com/databases. You don't have to retype all of the sample data if you don't want to. The sample customer table was built in Access 1, so you're welcome to use it as a starting point.

Let me break down how my courses are structured. There are four main tracks: Beginner, Expert, Advanced, and Developer.

The beginner section is designed for users who are new to Access. It gives an overview of essential features so you can quickly become productive.

Expert classes are for users with some experience who are ready to explore topics in much greater detail. Here, you will dig deeper into features and discover more power-user techniques.

Once you complete the expert level, the advanced lessons wait for you. These focus on macros, various automation features, and more sophisticated tools that can help you create really polished, professional databases.

Finally, the developer courses are all about teaching you to program with Visual Basic for Applications in Access. This will enable you to unlock advanced functionality and create truly robust databases.

Each series is structured into levels, starting at Level 1, where every subsequent level builds on what you learned in the previous ones. When you finish all the beginner lessons, move on to the expert series, and so forth.

In addition to these core Access seminars, I offer additional seminars targeting specific topics. For example, you can learn how to build web-based databases, design calendar-style forms and reports, secure your database, manage images and attachments, write work orders, track payables, and get an introduction to SQL, among many other subjects. Full details about seminars are available on my website.

If you ever have questions about the material, I encourage you to post in the student forums. Those of you watching the courses through my video player or the online theater will have easy access to see the forums alongside each lesson, provided you are connected to the internet. You will find questions from other students along with my answers and additional comments.

Please take some time to read through these discussions before starting each new lesson. I also invite you to post your own questions and comments so that everyone can benefit from our shared experiences.

For those not watching online, you can still visit the forums at accesslearningzone.com/forums.

To get the best results from this course, I suggest you watch each lesson all the way through first before doing anything on your own computer. Then watch the lesson again, following along with my examples. Try to recreate the sample database exactly as you see it in the lesson. Only after you have successfully mastered the sample database should you try to apply these skills to your own projects. If you find something confusing or get stuck, replay the video or post your question in the forum.

Remember, learning Access can feel challenging at first, but keep an open mind. Once you've spent some time with it, you'll see that it actually becomes quite intuitive.

Here is a quick overview of what you can expect in today's class:

In Lesson 1, we start with additional query criteria, focusing on working with multiple criteria and understanding how to use AND and OR to filter your data precisely.

Lesson 2 continues with query criteria, where you'll discover the NOT keyword, how to control which fields show in query output, and how to apply criteria to dates and times.

Lesson 3 covers advanced criteria, including working with IS NULL and IS NOT NULL, the LIKE keyword, and using wildcard searches.

Lesson 4 introduces parameter queries, allowing you to input values directly when the query runs.

Finally, in Lesson 5 I will answer questions submitted by students.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Query criteria basics
Using multiple conditions in queries
AND and OR conditions in queries
Working with inequalities in queries
Finding records between two values
Finding records between two dates
Working with null values in queries
Using wildcards in queries
LIKE keyword in query criteria
Creating parameter queries
Prompting users for query values
Showing and hiding fields in query results
Using the NOT keyword in queries
Working with date and time criteria
IS NULL and IS NOT NULL keywords in queries
Wildcard searches in queries

Article

Welcome to this Microsoft Access 2010 beginner tutorial where we will focus on building queries using advanced criteria and techniques. This article is designed for users who are new to Access or have some familiarity and want to deepen their understanding of working with queries. The topics covered here apply to Access 2010, but you will find them just as useful if you are using Access 2007 or even Access 2003, since the core concepts remain the same. While some menu locations differ between versions, the steps and methods described here will work for all of them.

We will start by exploring how to use query criteria to filter data effectively in your database. Queries are the primary way to extract and view specific sets of data from your tables. For example, suppose you have a customer table and you want to find all customers in a particular state or customers whose last names start with a specific letter. Queries let you do this quickly without the need to scroll through all your records.

To begin, open your database and create a new query in Design View, using your customer table as an example. Once you add the table to the query design grid, you can choose the fields you want to display in your results. To filter records, you set criteria in the Criteria row beneath each field.

If you want to find all customers from New York, type NY in the Criteria row under the State field. When you run the query, you will see only those records where the state is NY. To filter for more than one state, such as NY or CA, you can use the OR condition by typing NY on the first Criteria line and CA on the next, in the same column. This tells Access to include records if the value matches either condition.

AND conditions require that both criteria are met for the record to appear. To see all customers from New York with a last name that starts with the letter S, type NY under State and S* under LastName on the same Criteria row. The asterisk acts as a wildcard, telling Access to look for any last name beginning with S.

You can also specify ranges and inequalities. For example, if you want to find customers whose birthdays are between January 1, 1980, and December 31, 1990, you would type between #1/1/1980# and #12/31/1990# in the Criteria row under the Birthdate field. The pound signs let Access know you are referring to a date. If you want to see customers who are not from New York, you can use the NOT keyword and enter Not "NY" under the State field. Similarly, to get records outside a date range, you could enter Not Between #1/1/1980# and #12/31/1990#.

For searching blank or missing values, use the IS NULL criterion. For instance, to find all customers who have not listed an email address, type Is Null under the Email field. Conversely, to find all records where the email is filled in, use Is Not Null.

The LIKE keyword is useful for pattern searches. For example, to find all customers whose last names contain the letters "son" anywhere in the name, you would enter Like "*son*" under LastName. The asterisk again acts as a wildcard, matching any number of characters.

Sometimes you want Access to prompt you for a value every time you run the query. This is called a parameter query. Instead of entering a specific value, you can use square brackets to define a prompt. For example, instead of hardcoding NY under State, enter [Enter the state:]. When you run the query, Access will pop up a box asking you to enter the state, and it will search for whatever value you type.

To hide a field from your query results but still use it for criteria, uncheck the Show box for that field in the query design grid. This lets you use fields for filtering without displaying them in the results, keeping your output cleaner.

As you become more experienced with queries, you can combine these ideas to create very powerful searches. For example, you might want to find all customers from a certain state who signed up within the last year, who have provided an email address, and whose last name begins with a certain letter. By using combinations of AND, OR, NOT, wildcards, and parameter prompts, you can tailor your data search to virtually any need.

If you eventually want to take your databases to the next level, you might be interested in learning more about macros, automation, or even programming in Visual Basic for Access. These tools allow you to create even more advanced and interactive databases. For now, focus on mastering query design and criteria, as these are the building blocks for everything you do in Access.

To practice, create your own customer table with several records, or download a sample customer database if you do not want to type in the data. Try building the different types of queries described here and experiment with the criteria to see how your results change. If you get stuck or are unsure about how to create a particular search, review the steps, or try different variations. With practice, you will find queries become an essential part of your workflow in Access, giving you fast answers to your key business questions.

Remember, the key to successful learning is to be curious and to practice. As you work through building queries and using different criteria, patterns, and parameter prompts, you will develop a strong foundation in data analysis using Microsoft Access.

Primary Topics

query criteria, logical operators, AND/OR conditions, NOT keyword, date criteria, null values, wildcard searches, LIKE keyword, parameter queries

Secondary Topics

hiding query fields, student forum usage, database download, course progression