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.