Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, we're going to talk about filtering by date of birth.
What does that mean? Well, it's filtering by the customer, patient, or user's date of birth, so that when you go to look them up in a list, it's not a big, gigantic list with thousands of names in it. It basically makes it easier to find people.
Today's question comes from Owen in Sugarland, Texas, one of my Platinum members. Owen says, when I go to my doctor's office, they ask for my date of birth first. I assume that's to shorten the list of patients in their database, so when they ask for my name, it's on a much shorter list. I have the same situation with my database, patient records. Since I always have their date of birth, how can I limit the results so my combo boxes aren't filled with thousands of patients?
Well, Owen, this is a great technique. A lot of businesses use it to look up customers. If you don't have a key that you can index with no duplicates, like a phone number, social security number, client number, or order number, if you just got some bit of data like their date of birth, you can at least limit the list of results based on that.
If you've got 10,000 customers, odds are one in 400 or so will be on any one particular date. If you type in their date of birth first, which is perfectly acceptable for a business like a doctor's office, then you might only have three patients left. You can say, "Oh, what's your first name?" "Oh, I'm Richard." "Oh, hi. How are you doing? Here you are. I got you."
So, that's what we're going to do today. There are a lot of different ways to do this, and I'm going to show you a couple of different techniques. First, I'm going to show you a non-programming way, a non-VBA way, so this will be an expert-level class.
Owen is an expert-level student. What does expert mean? You're a little bit beyond the basics, so you're not a beginner, but you're not quite a developer yet, so you don't have to use any VBA for this technique. But you should know solid access basics.
Let me tell you some prerequisites first. We're going to use my TechHelp free template, which I show you how to build in this blank template video. You should know how to get a value from another form. So, if you've got a customer form open and you want to know how to get a value off the order form, that's what's covered in this video. Likewise, knowing how to have your query criteria get a value from an open form. If you don't know what query criteria are, go watch this.
I will also be setting up an alias in one of my queries. It's just another name for a field. If you haven't watched all these videos and you're not familiar with these concepts, go watch these and then come on back.
Here I am in the TechHelp free template. We're going to take this text box here, and we're going to make that equal to our date of birth filter. Then we're going to click the hello, hello, that button. We're going to click the hello world button, and then open up our customer list form, this thing, showing just the customers with that date of birth.
I already got a field in this table called customer since. We're going to cheat and pretend that's the date of birth field. Okay?
Let's go into design view. Let's change this guy to the DOB filter. I am going to change its name, come on, all the way up top here. Let's change your name to DOB filter. Let's get rid of the control source. The format of short date is fine, and I'm going to put a default value in here so that I don't have to keep typing in a date in here. So, I'm going to put in my birth date. I'm going to put in 1972-10-23, just like that. I use the ISO date format, which is year-month-day, that way it's not ambiguous. I am on a mission to change the whole world over to the ISO date format. Go watch this video for more information.
We could change this button so it says open customers by DOB or whatever you want to have on the button caption. In fact, we can delete this button now that I'm thinking about it. We have a button right here that opens up the customer list. Move that over there. Open customer list by DOB. Okay.
Now save this, close it, and open it back up again. Right now, if I open up my customer list, it shows everybody. This form is getting its data from the customer table. What we can do is make a query where the query says, "Hey, get your filter from this guy right here, and then show me a list of customers with that date of birth."
Let's make a query next. Create query design. Let's bring in the customer table. Where are you? Customer table right there. Bring in all the fields.
I'm going to cheat. I'm going to make a field called DOB. We already have a field in here called customer since. That's a date field. So, I'm just going to use that. Bring that over here, and we're going to alias it here. I'll zoom in so you can see it. Shift-F2. Oh, my zoom box got really big. There's my zoom box. We're going to go DOB: customer since. That's an alias right there. We're just saying, "Take the customer since field, and from this day forth, call it DOB." That way, we're just cheating and giving ourselves another field. See? It's over there. Now we can refer to it as DOB. It's the same thing as that one. Okay?
Now I can also tell the DOB field to get its criteria from that form field. The criteria, here I'll zoom in again. Well, I'm not going to zoom in because if you zoom in, you lose the IntelliSense. I'm going to go equals Forms! MainMenuF! DOBFilter, which is right there off the screen. See that? You can still type it in, you can see it better, but you don't get the little IntelliSense popup. That would be nice to add to the list, right, Sammy? Put that on our list for the Access team. IntelliSense in the zoom window. Why not? Because that can't be too hard to add.
Let's save this as the customer list query, and I'm going to close it and open it. Where's customer list? There it is. Open her up, and there is nothing in there. Why is that? Well, probably because no one has my birthday. Probably not even me. Let's go to the customer form. Customer since is the birth date, so I'll put in 1972-10-23. Let's give one other person, one other lucky person, like Malcolm Reynolds, my birthday too, so we have two people, 1972-10-23. Yes, I'm old.
Now, if I run this query, oh, look at that, there we are. Because we matched that date of birth. Now all we have to do is tell our customer list to get the values for itself from that query. So, open up the customer list, go into its controls here, go into its properties, and where it says record source, we're going to make this the customer list query that we just made. Save it, close it, close it, open it, and there you go. That's the easy way.
We can open up one of these other customers just by doing that, which we covered in the other videos. See, that's one easy way to filter based on a date of birth. Now keep in mind, you have to have this main menu form open, because if you try to open up the customer list form and the main menu isn't open, you're going to get enter parameter value because you can't find it. So you could, if you wanted to, at this point, type in 1972-10-23, and it will work. But you have to have that form open if you want to use this criteria.
Now, what if you don't have a default value in here? What if you don't have this value set, which normally you wouldn't. If you're having a, you know, entered patient information form, you might not want to have to enter their data, but they're just looking for someone. See, now you get no records if that's blank. Because you're telling the criteria, it's got to be equal to that. So who's got a date of birth that's equal to null? Nobody in there, I don't think.
So what do you do if this is blank or null? Well, now we're getting a little more advanced. We can use two more functions, the if function, immediate if, and is null. I got videos for these. If you want to go learn more about them, here's the if function, immediate if. And here's a video that will teach you about null, is null, not null, all that stuff. And if you want to learn about null, go watch this.
So what we can do is go into that query, design view, click on that criteria, zoom in. I mean, I am going to zoom in this time. This gets a little complicated. I'm going to copy this to my clipboard. Now we're going to say if is null that guy. If that's null, what you're going to use for the criteria for this record is the value of the record itself, in this case, customer since. Otherwise, the criteria is going to be the criteria on the form. And that's it. Tricky, I know. But that's just how it works. So if the criteria is null, then use the date of this record as its own criteria. So you'll always get the record. Because it is its own criteria. Otherwise, if this is not null, then use it as the criteria. This is a little more advanced stuff. I usually teach this in my higher expert-level classes. Save it. Close it. Open it up.
Now, what's that? Expression is typed in incorrectly, or it's too complex to be available. Okay. Well, what does this mean? All right. Don't hit debug. Hit it.
Let me explain to you what happened here. I'm going to leave this in the video because this happens a lot. This happens to me all the time. And it's just a quirk with the way that we wrote the statement. Let's go back into the query real quick, design view. And again, let's zoom in on that field.
Can you see what's different now? In fact, let me do this so it's easier to read right there. Can you see what's different from what we typed in? Notice the quotes. I put customer since in here. And Access, trying to be nice, converted that into a string. The actual value, customer since. I don't want the actual value, customer since. But if you do that in the zoom box, it puts quotes around it for you. If you do it down here, if I just put in customer since like this and hit tab, look at that. It puts the brackets around it. See, it fixed it for you. This is a lot smarter than the zoom box. So the answer is you got to have brackets around this so Access sees it as a field and not as a text string. And this happens a lot. This comes up a lot in the forums. I get emailed this question all the time. So I'm leaving it in the video so you learn and see it. Quotes and brackets, very important in Access.
Save it. If you run the query right from here, you should get results. And since there's no criteria in there, that's what you want. See, perfect. But if I do type in 1972-10-23 and hit customer list, there it goes. It's working. See that?
So there you go. That's the basics of how to filter by DOB. Now I told you at the beginning of the video that I'm going to show you the non-programmer way how to do this. But it's actually, in my opinion, easier to do this if you know a little bit of VB. Instead of having to make a separate query and use all that criteria, we can do it with a couple of lines of VB code much, much easier. We'll cover that in part two. We'll do the developer version of the same thing, but with some VB code.
This video is going public on Friday, August 30, 2024. We will cover this in part two. Monday is a holiday. It's Labor Day here in the States. So we'll cover it on Tuesday, September 3. Check back then for part two.
That is going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. Have a good weekend, and I'll see you on Tuesday for part two.
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. 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.
Give a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. 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.
Want to learn more? Click the "Show More" link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. Once you click on that, you'll 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 like they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list, and you can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.
If you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the "Join" button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my Code Vault, lots of VBA source code, template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the Tip Jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I got some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.
If you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over 4 hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. Did I mention it's completely free? The whole thing. Free. 4 hours. Go watch it.
And okay, okay, a lot of you have told me that you don't have time to sit through a 4-hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. And no, I didn't just put the video on fast forward. But I'll put a link to this down below as well.
Now, if you like Level 1, Level 2 is just a dollar. That's it. One dollar. That's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2. It's free.
Okay, want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page, and you'll have a better chance of getting it answered.
And while you're on my website, be sure to stop by my Access Forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now, let's talk more about those member perks if you do decide to join as a paid member. There are different levels: Silver, Gold, Platinum, and Diamond. Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks, plus access to download the sample databases that I build in my TechHelp videos, plus access to my Code Vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list for me to read them, and if I like your question, you got a good chance of it being answered. You'll also get one free expert level class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
And finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.
For a complete video tutorial on this topic, please visit https://599cd.com/FilterByDOB?key=Dev.To
Top comments (0)