Filter By DOB 2
By Richard Rost
2 years ago
Filter Customer Lists by DOB in Forms, Combos, Part 2
In this Microsoft Access tutorial, I will show you how to filter a customer list by date of birth using VBA. We'll start from scratch and cover techniques like using a where condition and filter properties to make your list shorter and more manageable. This is part two.
Members
In the extended cut, we will learn how to apply a date of birth filter to a combo box. This will allow you to filter customer lists within combo boxes based on specific dates, streamlining the selection process for users.
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!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, Microsoft Access, filter by date of birth, VBA date filter, Access customer list, customer DOB filter, Access forms filtering, Access combo box filter, date filtering in Access, VBA where condition, Access filter property, Access form filter by date, filter combo box by date, date of birth query
Intro In this video, I will show you how to filter a customer list by date of birth using VBA in Microsoft Access. We will cover setting the form's record source back to the original table, checking for null input in your filter, and how to use both the where condition and filter property methods in VBA. I will also explain how each method works, touch on formatting date fields, and highlight some differences in performance and behavior between them. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my Filter by Date of Birth series where I teach you how to filter a customer list by their date of birth first to make the list shorter and easier to find. This is part two where we're going to do the developer VBA way to find the customer list. If you haven't watched part one yet, well, that's okay, you don't have to if you don't want to see the non-VBA way to do it. Today we're going to pretty much start from scratch and do the same thing using some VBA. So strap in, here we go.
Alright, so in part one we made a date of birth filter box and then our customer list just opens up based on this query and the query has the criteria in it. Then, the customer form, customer list form gets its data from the customer list query. Well, if you know a little bit of SQL and VBA, it's actually, in my opinion, easier than having to go through all this. You just open up the form directly, getting the data from the table, straight out of the table, and you can either add filtering using a where condition or you can use a filter property.
Before we get started, this is a developer-level video, so if you've never programmed in VBA before, go watch this. It'll get you started, and teach you everything you need to know in about 20 minutes. You'll need to know how to do an if-then statement. If you don't, go watch this video, and if you're not familiar with how to use the filter properties, go watch this video.
Alright, so the first thing I'm going to do is leave this query here in case you guys want to have it for the database, gold members, but we're going to take our customer list F, this thing, and put the record source back to where it was before, customer T. We don't need a query to do what I'm going to show you how to do right now. Save that, close it.
Alright, this button currently opens up the customer list, but I believe if memory serves, it's using an embedded macro. Let's see, build event, and oh no, it's using code, okay. So when you build buttons using the command button wizard, it builds embedded macros, which I teach in the beginner classes because they're easier, but you really want to use VBA if you're going to be doing any serious development. So this is right here how it opens up that form.
Now like I said, there are two ways to do it. You can either use a where condition or you can use the filter properties. First thing I want to do though is check to make sure that the user has typed in a filter. If not, we're going to have the same problem we had in the last class where if it's null, you're going to see no values. So we're just going to first say if is null DOB filter, then just do that. Open it up normally without any criteria or whatever, okay. Otherwise, else, do something else. Now it's going to start off the same. Okay, it's going to start off with that. And again, there are two ways to do it here. You can use a where condition, which is comma, comma, comma right there, where condition. Ignore this thing here that says filter name. It doesn't work the way you think it works. It requires a query, and it just, no, I wish this didn't even exist. Ignore that and never use it. What you want is where condition, okay. And here's where you can put what you want for your filter, for your where condition.
So, in this case, I'm going to say customer DOB equals and then in pound signs, because it's a date field, DOB filter and pound sign. That's it. That's the easy way to do it. Alright, takes that DOB filter, whatever it is, you know, 1972, 1023, puts it inside of these guys. So, it's a valid date and then sends this whole thing as a where condition, okay. Someone's beaming in, hold on, energize. For those of you who are new, that's just my hourly chime that goes off in the background.
Okay, debug, come, oh, debug compile. There we go. Come back out. Yeah. Close it. Save changes. Yes. Open it up. Hit that. It just opens normally. But if I put my date of birth in here, 1972, 1023, hit it and there I am. And Malcolm Reynolds too. Okay, so that works. Let me show you the second method in case you want to use the second method. Which one's better? I don't know. I've always used this method myself, but it's totally up to you if you want to use the other method. So, I'm going to just rem this out and leave it there. So again, you've got it in the database if you download it.
Okay, the second method is going to be used to filter properties. Basically, the same thing. So I'm just going to copy this, put it there. Here, I'll put some notes in here too. Alright, other condition method, okay. And then the next one here is going to be the filter property method. Lots of ways to do things in Microsoft Access. So, you're going to open the form and then you're going to say forms customer F dot filter equals some stuff. What's the stuff? It's this stuff right there. Copy it, paste it, okay. So, you're setting that in the filter property. Then you just got to make sure the filter is on forms customer F dot filter on equals true. And that's it. That's all you got to do. It's not hard. It basically has the same effect as the other one. There are some minor differences, but don't worry about it. Right. Boom. Oh, I got it on. Oh, my bad. Anybody see what I did wrong? I'm going to leave this in the video. See what I did there? Take a moment, pause the video, the learning experience for you guys and an experience for me. I see sometimes I'm talking to you and I'm not, my brain doesn't pay attention. Right. Can't find customer F. Why? Well, because I'm not working with customer F. I'm just so used to typing in customer F because 99% of the examples that I teach in these classes are customer F and not customer list F. Save it. And that's one of those things. That's a runtime error, folks. That's not something that compiles, compilers are going to find. It's not a compile time. It's not a syntax error either. Alright. Now let's try it. And okay, there it goes. You can see the records all show up briefly and then they flash away like that, right. If you watch it carefully, watch, you see all the records and then they get filtered. Whereas with the other method, if you rem these ones out and you do it with this one, it doesn't do that, watch. Boom. It just opens up faster. It is a little quicker this way because the other one loads all the records up and then it applies a filter, whereas with this method, it literally just only loads the records that you need. It's still a filter. You can still unfilter it and then it will go and load the rest of the records. So from the user standpoint, it's pretty much the same thing.
Now, what if it's not a form that you want to open? What if it's a combo box, which was what the original question, I believe, mentioned was using it with a combo box as well? We could put a date of birth filter here on any form you want, like this combo box. And this combo box normally has a big, giant long list of customers in there. But if you're going to create a new one and you want to filter this new record, let's say a new order, right, and I'm going in here and I got thousands of customers. Well, it'd be nice if I could filter them in here. 1972, 1023. And then, oh, look at that. I just filtered that combo box. Well, I will show you how to do that, ladies and gentlemen, in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. Gold members can download these databases, and they get access to the code vault, and everybody gets free lessons every month. And it's just a wonderful, joyous thing to be. So click that join button today.
And that is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsors. First, we have Juan Soto with Access Experts Software Solutions, manufacturing experts specializing in access and SQL server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com. Another shout out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist, and he not only offers access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your access project, Sammy is your guy. Check him out at ShamaConsultancy.com.
TOPICS:
Filter a customer list by date of birth using VBA
Setting form record source to original table
Checking for null filter input in VBA
Using the where condition in VBA
Formatting date fields in VBA filter
Using the filter property in VBA
Opening forms with filter properties
Handling runtime errors in VBA
Comparison of where condition and filter property methods
COMMERCIAL:
In today's video, I will show you how to filter a customer list by their date of birth using VBA in Microsoft Access. We start by revisiting the non-VBA method from part one, then we dive into the VBA approach. You will learn to switch your customer list form to fetch data directly from the table and apply filters efficiently. From handling null filters to using where conditions and filter properties, this tutorial covers the essentials. If you're a beginner, a quick primer on VBA basics will get you started. Catch the complete video on my YouTube channel and my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the focus of part two of the Filter by Date of Birth series?
A. Non-VBA methods to filter by date of birth
B. Developer VBA methods to filter by date of birth
C. SQL query optimization
D. Beginner-level form creation in Access
Q2. What pre-existing knowledge is recommended before watching this video?
A. How to build embedded macros
B. Basics of SQL and VBA programming
C. Setting up a SQL server
D. Advanced Excel techniques
Q3. How is the customer list form initially set up to retrieve data?
A. Directly from the customer T table
B. From the customer list form
C. From the customer list query
D. Using a filter on the customer DOB field
Q4. What should you check before applying a filter in VBA to avoid errors?
A. If the form is saved
B. If the table exists
C. If the filter property already has data
D. If the DOB filter value is null
Q5. In VBA, how is a where condition used to filter data by date of birth?
A. Adding it as a text filter
B. Using embedded macros
C. Setting the filter property directly in the form
D. Writing the condition in the where condition parameter
Q6. When setting the filter property in VBA, what must be done after defining the filter condition?
A. Closing the form
B. Compiling the code
C. Setting the filter on property to true
D. Saving the filter in a query
Q7. Which method typically opens the form faster and only loads the necessary records?
A. Using the where condition
B. Using an embedded macro
C. Directly filtering the table
D. Setting the filter property
Q8. What does Richard mention needs special attention to avoid a runtime error?
A. Typing the correct form name
B. Ensuring the database is backed up
C. Verifying the data types in the filter
D. Adding error handling code
Q9. What is the purpose of filtering a combo box by date of birth?
A. To delete unnecessary records
B. To display all possible records
C. To make the list of items in the combo box shorter and more manageable
D. To create a new query for the combo box
Q10. What additional content is available in the extended cut for members?
A. Advanced Excel tutorials
B. Filtering a combo box by date of birth
C. Access installation guides
D. SQL server integration tips
Answers: 1-B; 2-B; 3-C; 4-D; 5-D; 6-C; 7-A; 8-A; 9-C; 10-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 TechHelp tutorial from Access Learning Zone focuses on filtering a customer list by date of birth using VBA in Microsoft Access. This lesson is the second part of my Filter by Date of Birth series. In this installment, I will show you how to use developer techniques and VBA code to make filtering your customer list by date of birth both easier and more efficient.
In the first part of this series, I demonstrated how to filter a list based on a date of birth using traditional queries and form controls. However, if you are comfortable with SQL and VBA, I think you will find that applying these tools can streamline the process. Rather than relying on a query to filter your results, you can open your form directly and use VBA to either apply a where condition or set the filter property of the form.
Before proceeding, keep in mind that this is a developer-level tutorial. If you have not programmed in VBA before, I recommend watching my beginner video on the topic. You will need to understand essentials like If-Then statements and how the filter property works in Access forms.
To get started, I am going to leave the original query in the database for those who may still want to use it, but I will revert the customer list form so that its record source points directly to the customer table rather than a query. This adjustment means the form will show all customers unless a filter is applied with VBA.
The button on our form that opens the customer list previously used an embedded macro, but it turns out this database is already set up to use VBA code for that operation. When you create buttons with the command button wizard, it tends to default to embedded macros, which are fine for simple uses and beginners. For anything more advanced, and certainly for what we are doing today, using VBA coding is much better practice.
Now, there are two main ways I can show you to filter the records. First, you can use a where condition when opening the form. Second, you can use the form's filter property. Before even applying a filter, though, it is good practice to check if the user has actually entered anything in the filter box. If the filter field is empty or null, the form should just open normally, displaying all records. If the user has entered a date, only then do we proceed to apply a filter.
For the where condition approach, you construct a filter string where the customer DOB matches what has been typed into the filter box. Because we are dealing with dates, be sure to format the value properly so Access recognizes it as a date. This filter condition then gets passed directly into the OpenForm command.
The second approach uses the form's filter property. Here, you open the form normally, and then, in code, you set the filter property of the form to the same condition as before. After setting the filter string, make sure the filter is turned on by setting the FilterOn property to true. The end result is practically the same from the end user's perspective, but there are small differences. For instance, applying a filter property means the form loads all the records first and then filters them, which can make for a slight delay on large datasets as you briefly see all records before they disappear. The where condition filters the data before the form opens, so only the needed records are loaded initially, making it somewhat quicker.
Sometimes you may encounter runtime errors, especially if there are typos in form names or field names. For example, if you reference a form or control incorrectly, you might get an error that does not show up during compilation, but only when you actually run the code. Access does not always catch these mistakes before you execute your program, so keep an eye out for such issues during testing.
You might be wondering how to apply similar filter techniques to other controls, such as combo boxes. For example, if you have a form for new orders and need to find a customer quickly from a very long list, filtering that combo box by date of birth would be helpful. In today's Extended Cut, I will go into detail on how to filter combo box lists based on user input, like date of birth, to make customer selection much faster and easier.
This tutorial covered several key techniques: changing the record source for a form, handling null filter input with VBA, using where conditions and the form filter property, formatting dates in filter conditions, opening forms with filters, managing runtime errors, and when to use each method depending on your needs.
If you want to see step-by-step instructions and demonstrations for everything discussed here, I have a full video tutorial available on my website at the link below. Live long and prosper, my friends.Topic List Filter a customer list by date of birth using VBA
Setting form record source to original table
Checking for null filter input in VBA
Using the where condition in VBA
Formatting date fields in VBA filter criteria
Using the filter property in VBA
Opening forms with filter properties
Handling runtime errors in VBA
Comparison of where condition and filter property methods |
