Filter Records
By Richard Rost
5 years ago
Filter Form Data to Find Records Quickly & Easily
In today's video, I'm going to teach you how to use a Filter in Microsoft Access to limit the data displayed in your forms, and search for just the information you want quickly and easily.
Lewis from Cheektowaga, New York (a Gold Member) asks: I've got a few thousand customers in my customer table. I need to be able to quickly and easily filter this list and I don't want to make a bunch of queries because my needs change from moment to moment. One time it might be customers from NY with $1000 credit limits. Another time it might be anyone from the 716 area code with the last name Smith. What do you recommend?
Members
I will show you how to create a combo box on your customer list form to save all of your favorite filters. Then we will learn how to add criteria to that with the Replace function and a little variable substitution. Then we will learn how to set up the filter in the form itself and SAVE it to the table the combo box is based on, updating the box. Really cool stuff in this lesson!
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Links
Recommended
Subscribe to Filter Records
Get notifications when this page is updated
Intro In this video, I will show you how to quickly and easily filter records in your Microsoft Access tables and forms, making it simple to find the data you need without creating multiple queries. We will look at different filtering options for text, numbers, and dates, including how to use built-in filter tools to search by specific criteria such as state, credit limit, or last name. I will also explain the differences between filtering in tables versus forms and share tips for managing your data efficiently in Microsoft Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to filter your Microsoft Access Form data to search for records quickly and easily.
Today's question comes from Lewis from Chick-Dawaga, New York, my old stomping grounds. A Gold Member, Lewis says, I have got a few thousand customers in my customer table. I need to be able to quickly and easily filter this list, and I do not want to make a bunch of queries because my needs change from moment to moment. One time it might be customers from New York with a thousand dollar credit limit. Another time it might be anyone from the 716 area code with the last name Smith, what do you recommend?
Well, Lewis, you could make some queries with some query criteria. I have other videos on that. I will put a link down in the description below. But it sounds to me like you really just need to get a good grasp on how to use filtering, either in your customer table directly if it is just you, or in your customer list form if you have other people that are going to be using it as well.
So, let me show you some techniques on filtering in your tables, queries, or in your forms directly.
Here I am in my TechHelp free template. This is a free download off my website, and you can go grab a copy if you want. I will put a link down below in the description.
Filtering is something you can do at the table level. But I generally tell people do not work directly with your tables, especially if you have other people using your database. You do not want people poking around inside your tables.
If you go up here on the Home tab, you will see this section called Filtering.
If you want to filter, say, by state, like New York, come over here, click on the state, click on the state that you want like New York right there. Hit the filter button. You will see this thing appears. You can do sorting in here, A to Z. You can sort by a specific state. For example, if you want all of them, right now it is select all. You can turn that off and say just give me New York.
Now when I hit OK, there is New York. And you can see this little icon pops up there telling you that it is filtered, state equals New York. Click that button again and it opens up the filter window again.
If you want to see New York and Florida, that can be a whole lot quicker than building a query, especially if your needs change from time to time.
Drop it down again, go to select all. Want to see just the blanks? Click on blanks and you will see anybody who does not have a state, where the state is null, as we like to say.
There are text filters here. Let me open that up for you. This has a bunch of options in here. For example: equals, does not equal, begins with.
This actually is easier and makes more sense if I show you with a name. Let me show you with a name like here at Picard. Let me turn this filter off first of all. Drop this down, go back to select all. That effectively turns off the filter.
There is also an option down here in the bottom. Right now it says no filter. If I put a filter on, say, city, and I want to see anybody but Buffalo and Fort Myers, there you go. Now you see how it says filtered down here. You can turn that off right there, and now it is unfiltered.
But let's say I want to see anybody from the amacron.com domain. Select amacron.com, just like that. Right click on it and go down to contains amacron.com or even ends with amacron.com. See that? And there is another filter for you.
Click this, go to text filters. There is a whole bunch of them in here: equals, does not equal, begins with, does not begin with, contains, does not contain, ends with, or does not end with. See all those different types that are in there?
You want last names beginning with the letter R. Right click, begins with R. See that? And then I will turn that filter off.
Now numbers and dates are treated differently as well. If you come over here to the number fields, say this credit limit is a number. Currency and number values get treated pretty much the same.
Drop this down for credit limit, go to number filters. You have equals, does not equal, less than, greater than, and between.
Between is a handy one - you could say between two values, let's say 2000 and 4000. This is inclusive, by the way, so it will include the endpoints. Hit OK, and there is your 4000. See that? Turn it off.
Same thing pretty much with dates. Here are dates right there. For dates, there is a whole bunch of options. Go to date filters. Look at all these. You have equals, does not equal, before, after, between, tomorrow, today, yesterday, next week, this week; you have all kinds of stuff in here. Look at how many there are.
Past, future, last year, this year, all dates in period, even more options - months, quarters, all this stuff.
Now again, these filters work in your tables and they also work in your forms too. You can open up a form like this, pick a field like that, right click on it, and then you have your filters down here - equals Florida, does not equal Florida. Text filters over here: contains, does not contain, all the same stuff.
Again, if it is just you, you can go ahead and use the table. If you have other people working with your database, I strongly suggest you limit them to just forms.
Now, in my Access Beginner 1 class, I have a whole lesson here on sorting and filtering. This is absolutely free, this whole class, three hours long, it is on my website, it is on my YouTube channel. Watch this, it goes over sorting and filtering, entering data, all this different stuff.
I cover filtering in there just like I showed you now.
Want to learn more about filters? In the extended cut, which is 30 minutes long for the members, I show you how to make a little combo box on your customer list form that has all of the popular filters that you like. Maybe you have credit limit over a thousand, customer before the year 2000, whatever. Then we will make it so you can include a blank in there.
What's the blank? Whatever you type in this box over here. So, last name includes, or first name starts with J, credit limit is less than blank. Put the blank in here, hit the filter button, and it applies that filter.
Then we go another step further. We can apply filters up here with the right click, like I showed you in this video: right click, maybe state of Florida, credit limit over a thousand, whatever. Once you have that set up, I want to save that to this box. So, that is what that Save Filter button does. You set up the filter however you want it, hit Save Filter, and it saves it in that combo box so you can use it in the future. So any time you come up with something cool, save it in the box.
It is all covered in the extended cut for members, 30 minutes long. Silver Members and up get access to all of my extended cut videos. Gold Members and up can download this video, or download the template of the database that I built from my website, so no typing. That is all covered in the extended cut and you get access to all of my previous extended cut videos. I think there are over 100 of them now. We have over 100 TechHelp videos. I think we are over 100 extended cuts on that.
How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available.
Silver Members and up get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold Members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use.
Not only do you get all the previous perks, but also access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
Do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select all to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar and it is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is one advantage of using filters over building queries in Microsoft Access when searching for records?
A. Filters allow quick, flexible searching without creating new queries for every need
B. Queries are faster to set up than filters
C. Filters cannot handle multiple criteria
D. Filters are only available in reports, not forms or tables
Q2. Why does the instructor recommend against working directly with tables when other people are using your database?
A. Forms load faster than tables
B. Tables cannot be filtered at all
C. You do not want other users poking around or making unintended changes in your tables
D. Forms are the only place where number fields can be filtered
Q3. When filtering text fields in Access, which of the following is NOT a type of text filter you can apply?
A. Begins with
B. Contains
C. Sum of
D. Ends with
Q4. Where can you find filtering options on the Access Ribbon interface?
A. Create tab
B. Home tab
C. External Data tab
D. Database Tools tab
Q5. How can you filter a list to only show customers from both New York and Florida?
A. By creating and saving a new query each time
B. By clicking on the filter dropdown and selecting only New York and Florida
C. By typing both state names in the search bar separated by commas
D. By removing all filters and using a form
Q6. What does selecting "blanks" from a filter dropdown show in your records?
A. Records where the field is not empty
B. Records where the field is null (empty)
C. Records with numeric values only
D. Archived records
Q7. What is special about the "between" filter option for numbers and dates in Access?
A. It only shows exact matches for both values
B. It is exclusive and does not include the endpoints
C. The filter is limited to two values only
D. It is inclusive and includes the specified endpoint values
Q8. In forms, how can you quickly filter by a specific field value such as "Florida"?
A. Use the sort option in the form's design view
B. Right click the field value and choose the appropriate filter from the context menu
C. Only table views allow filtering by value
D. By running a macro
Q9. What extended feature is demonstrated in the extended cut for members according to the video?
A. How to format reports
B. How to make a combo box that saves and reuses custom filters
C. How to import data from Excel
D. How to design database relationships
Q10. If you want to save a filter you use frequently for future use, which method is shown in the extended video?
A. Save as a new table
B. Write down the criteria and enter it each time
C. Use a Save Filter button to store it in a combo box
D. Use Access macros to automate filtering
Q11. Which of the following is a benefit Gold Members get according to the video?
A. Access to TechHelp videos only
B. Download folder containing sample databases and code vault access
C. Only access to YouTube comments
D. Free personal training sessions
Q12. What does the instructor recommend for beginners who want a detailed lesson on sorting and filtering?
A. Start with advanced queries
B. Watch the free Access Beginner Level 1 course available on his website or YouTube channel
C. Read the Microsoft Access help file
D. Only use prebuilt templates
Q13. What indicator shows that a filter is currently applied in Access?
A. The background of the form turns yellow
B. A small icon appears indicating filtering
C. The database closes automatically
D. The record count becomes zero
Q14. What should you do to turn off a filter that you have applied in Access?
A. Delete all records
B. Click the "unfiltered" indicator or choose "Select All" from the dropdown
C. Close and restart Access
D. Convert the table to a query
Q15. How can you get notifications when new videos are posted, according to the video?
A. Subscribe to the channel and click the bell icon for all notifications
B. Check the YouTube Discover page every hour
C. Sign up for paid advertisements
D. Filters automatically notify you of new videos
Answers: 1-A; 2-C; 3-C; 4-B; 5-B; 6-B; 7-D; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-A
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 explores how to efficiently filter data in your Microsoft Access forms, allowing you to quickly find the records you need without having to constantly create and manage different queries.
I recently received a question from a viewer who has thousands of customer records in his database. He wanted an easy way to filter his list of customers for different criteria on the fly, rather than creating a new query each time his needs changed. For example, sometimes he may need to find customers from New York with a certain credit limit, while other times he might look for people in a specific area code or with a particular last name.
While building queries with specific criteria can solve this problem, and I have covered that in detail in previous videos, often the best solution is to master filtering techniques. Filtering can be performed directly in your tables, queries, or most effectively in your forms, especially when multiple users are involved. I do recommend that if you share your database with others, restrict them to working with forms rather than opening tables directly, which helps maintain data integrity.
Let me walk through the basics of filtering in Access. With your data open, you will notice there is a filtering section available. If you want to filter by state, for example, you can select the desired state, apply the filter, and instantly see only those records. You can even select multiple states or look for records with blank values in specific fields, which is a quicker approach than creating multiple saved queries.
For text fields like names or city, Access offers robust filtering options. Besides filtering for exact matches, you can also use filters such as "begins with," "contains," or "ends with" to narrow your results. This is particularly useful if you're searching for common values like last names starting with a specific letter, or email addresses containing a domain such as amacron.com.
Numbers and dates have their own filtering tools. With numeric fields like credit limits, you can filter for values greater than, less than, equal to, or even between specific amounts. Access treats currency and number values the same way here. With dates, you have even more flexibility: filter for records before or after a certain date, within a date range, or even use special options such as "last year," "this month," "next week," and more.
These filtering tools are not limited to tables; you can use the same approach in your forms. Right clicking on a field in your form allows you to access detailed filter options, making it convenient for anyone entering or reviewing data to focus on just the records they need.
For those who want to learn even more, in my Access Beginner 1 class, I teach the fundamentals of sorting and filtering, along with other entry-level topics. That course is freely available on my website and YouTube channel and provides a comprehensive introduction to Access.
For members, today's extended cut goes a lot further. There, I demonstrate how to add a combo box to your customer list form, which can store frequently used filters. For example, if you regularly search for customers with a credit limit above a certain amount or with records before a particular year, you can set those up as preset filters. I also show how to add a blank space in the combo box, so you can type your own filter criteria on the fly—perhaps for last names that include particular letters, or credit limits less than a certain value. When you come up with a filter that you would like to save for future use, you can save it into the combo box with a button click, making repetitive tasks much simpler. Everything is explained in detail for Silver Members and up, with downloadable templates available for Gold Members and up.
Membership offers access to all of my extended cut videos, live sessions, downloadable sample databases, and even my code vault packed with functions and examples. Higher tier members also unlock full versions of my courses not just in Access but also Word, Excel, Visual Basic, ASP, and more.
Of course, my free TechHelp videos will continue as always. If you find these lessons useful, consider subscribing to my channel, liking the video, or leaving your comments. For updates, check the additional links and resources listed below the video, or sign up for my email list to be notified whenever I publish something new—since YouTube no longer emails subscribers automatically.
If you have not already tried my free Access Level 1 course, I encourage you to do so. It is a thorough three-hour guide to getting started with Access, with Level 2 available for just a dollar or for free if you are a YouTube member at any level.
If you have a question you want answered in a future TechHelp video, visit my TechHelp page and submit it there.
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 Filtering data directly in Microsoft Access tables
Using the Filter feature on the Home tab
Filtering by specific field values in tables
Selecting multiple values to filter by (such as more than one state)
Filtering for blank (null) values in a field
Using text filters: equals, does not equal, begins with, contains, ends with
Filtering records by string patterns (e.g., names that begin with a letter)
Removing filters to display all records again
Number and currency field filters: less than, greater than, between
Date field filters: specific dates, ranges, periods (week, month, year)
Using form view to filter records in Access forms
Right-click filters in form view
Differences between filtering in tables vs. forms
Recommendation to limit users to forms for filtering |
