Filter By DOB
By Richard Rost
2 years ago
Filter Customer Lists by DOB in Forms/Combo Boxes
In this Microsoft Access tutorial, I will show you how to filter long customer lists by date of birth in forms and combo boxes. We will learn how to limit results to make it easier to find specific patients in a database, using non-programming methods.
Owen from Sugar Land, Texas (a Silver Member) asks: 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 that when they ask for my name, I'm on a much shorter list. I have the same situation with my database (patient records). Since I always have their DOB, how can I limit the results so my combo boxes aren't filled with thousands of patients?
Members
There is no extended cut, but here is the database download:
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
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, filtering customer list by date of birth in Access, date of birth filter Access, Access combo box filter, query by DOB Access, Access forms DOB filter, limit combo box results Access, Microsoft Access patient records, Access database date of birth query, without VBA Access filtering, non-programming Access tutorial, Access query design filter date
Subscribe to Filter By DOB
Get notifications when this page is updated
Intro In this video, I will show you how to filter a customer list by date of birth in Microsoft Access without using VBA programming. You'll learn how to use the TechHelp free template, set up a date of birth filter on a form, create a query that pulls its criteria from that filter, handle situations where the filter is blank or null, and set up aliases for fields in your queries. We'll also talk about troubleshooting common query errors and changing your form's record source based on that query. This makes finding customers in large databases much more manageable.Transcript 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.
TOPICS:
Filtering customer list by date of birth
Using date of birth to limit combo box entries
Non-VBA method for filtering by date of birth
Using TechHelp free template
Setting default value for date of birth filter
Creating a query to filter by date of birth
Alias a field in a query
Applying query criteria from a form field
Handling null values in query criteria
Using the IIf function in a query
Troubleshooting query criteria errors
Changing record source for a form based on query
Opening customer list filtered by date of birth
COMMERCIAL:
In today's video, I will show you how to filter a list of customers, patients, or users by their date of birth to make searching through thousands of names easier. Using a question from Owen in Sugarland, Texas, we'll explore several techniques without needing VBA programming. You'll need to know some Access basics, such as creating queries and using form values as criteria. I'll guide you through using the free TechHelp template, setting up date of birth filters, and fine-tuning queries to show specific results. You'll find 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 main purpose of filtering by date of birth in a database?
A. To display the full list of customers.
B. To shorten the list of results and make it easier to find a specific person.
C. To eliminate the need for other search criteria.
D. To sort the list of customers alphabetically.
Q2. In the tutorial, why is it suggested to use the date of birth as a filter in a doctor's office?
A. Because the date of birth is the only unique identifier available.
B. Because it is easier to remember than other identifiers.
C. To narrow down the patient list before asking for the name for quicker lookup.
D. To verify patient insurance information.
Q3. What level of expertise does the video target for this date of birth filtering technique?
A. Beginner
B. Intermediate
C. Expert
D. Developer
Q4. Which format does Richard recommend for entering the date of birth to avoid ambiguity?
A. MM-DD-YYYY
B. DD-MM-YYYY
C. YYYY-DD-MM
D. YYYY-MM-DD
Q5. What is an alias in the context of the video's explanation?
A. A secret identifier for a table.
B. Another name for a field in a query.
C. A function used to sort data.
D. An external variable to hold data.
Q6. What happens if you run the query with an alias without having brackets around a field name?
A. The query will automatically fix the field name.
B. The query will generate an error or unexpected results.
C. The query will ignore the alias.
D. The query will run faster.
Q7. What should the default value of the date of birth filter be set to according to the tutorial?
A. 01-01-1970
B. The instructor's birth date
C. A common date like 07-04-1990
D. The ISO date format-specific date like 1972-10-23
Q8. When modifying the criteria in a query, which function does Richard introduce to handle null values?
A. Nz
B. IIf
C. IsEmpty
D. IsNothing
Q9. If you see an "Enter Parameter Value" dialog when opening a form, what is likely the issue?
A. The form is corrupted.
B. The criteria field in the query is not found because the main form is not open.
C. There is a syntax error in the query.
D. The database is outdated.
Q10. What is the primary benefit mentioned about using a little bit of VB code instead of designing a separate query for filtering?
A. It provides more control over the user interface.
B. It's much easier and requires fewer steps.
C. It can handle larger datasets more efficiently.
D. It allows the use of custom form controls.
Answers: 1-B; 2-C; 3-C; 4-D; 5-B; 6-B; 7-D; 8-B; 9-B; 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 is all about filtering your customer or patient list by date of birth. I'm going to show you how to set up your Access database so that you can easily narrow down thousands of records and make it simple to find the person you're searching for. This technique is especially useful in scenarios like medical offices where staff usually ask for a patient's date of birth before searching for their record, helping them avoid scrolling through a massive list.
I recently got a question about this from one of my viewers who wanted to know how he could limit his combo boxes from showing thousands of patients at once in his Access database, given that he always has their date of birth on file. This is a common problem, especially when you don't have a unique identifier like a phone number, social security number, or customer ID handy. Even with just a date of birth, however, you can dramatically reduce the number of possible matches, making it much easier to pinpoint the right person in your database.
For example, if you have 10,000 customers, on average you'll have about 25 individuals born on any particular day. By first entering the date of birth, you can then ask for the first name or other details, narrowing down your results to just a couple of possibilities.
This lesson is aimed at Access users who have moved beyond the beginner stage. We're going to use a method that does not require any VBA programming, so if you're comfortable with building forms and setting basic query criteria, you'll be able to follow along. Before we get started, you'll want to know the basics of using query criteria, referring to values from other forms, and setting up field aliases. If any of these topics aren't familiar to you, I recommend taking a look at my earlier tutorials on those subjects, then returning to this lesson.
In this demonstration, I'm using my TechHelp free template as the foundation. We'll take an existing text box on the main menu form and set it up as a date of birth filter. I'll rename the text box to something meaningful, like "DOBFilter," and remove its control source so we can use it as an unbound filter input. For convenience during this example, I'm going to set a default date value in ISO format (year-month-day), such as 1972-10-23, to avoid manually entering it each time. Using the ISO date format avoids confusion since it isn't ambiguous.
The next step involves making a button that will open your customer list filtered by the entered date of birth. Instead of creating a new button, you can simply modify the label of an existing one for clarity.
Once that's set up, you'll notice that opening the customer list form currently displays all records. Instead, we want to filter these results based on the value entered in the DOB filter. To make this happen, we create a new query based on the customer table and add all its fields. Suppose your table has a "CustomerSince" date field, which we'll use as a stand-in for the date of birth. We can set up an alias in the query to rename "CustomerSince" to "DOB" for clarity.
Next, the key part is setting the query criterion for DOB to the value from the form's DOB filter. In the query's criteria row, refer to the form value directly. This will cause the query to only return records where the date matches the input.
Be aware that for this filter to work, the main menu form must be open because that's where the input resides. Otherwise, Access will prompt you to enter the parameter value manually.
A challenge you might run into is what happens when the DOB field is left blank. If the filter is empty, the query will search for records with a null date of birth, which likely returns no results. To handle this, you need to allow the query to return all records if the filter is empty. This is where you use a slightly more advanced technique that employs the Immediate If (IIf) function in conjunction with IsNull. The idea is that if the filter is blank, tell the query to match each record's date of birth to itself, which is always true, thus returning all the records. Otherwise, only return those records that match the provided date.
When writing this expression in the query, it's important to use brackets around field names so Access recognizes these as references, not text strings. If you make a mistake and use quotes instead, Access will interpret it as a literal string, not a field, causing errors. Brackets are crucial for making this work properly.
Once this is implemented, the customer list will correctly show all records when the filter is blank, or it will show only those matching the entered date of birth when specified.
The approach I demonstrated today lets you filter by date of birth entirely without VBA. Of course, if you're comfortable writing a bit of code, accomplishing the same thing in VBA is actually a bit easier and more flexible. I'll be covering the VBA approach in the next lesson, so stay tuned for that.
If you want step-by-step, visual instruction on everything discussed here, including setting up the filter, creating the query, using aliases, and troubleshooting common pitfalls, be sure to visit my website. There you'll find the complete video tutorial with all the details.
Live long and prosper, my friends.Topic List Filtering customer list by date of birth
Using date of birth to limit combo box entries
Filtering records without VBA
Using the TechHelp free template
Setting a default value for a date filter text box
Creating a query to filter by date of birth
Aliasing a field in a query
Applying query criteria from an open form
Referencing form controls in query criteria
Handling null values in query filter fields
Using the IIf and IsNull functions in queries
Fixing common errors with query criteria syntax
Changing a form's record source to a filtered query
Opening a filtered customer list form based on date of birth |
