Highlight Birth Month
By Richard Rost
13 months ago
Change Text Box Color for Birth Month in MS Access
In this Microsoft Access tutorial, I will show you how to use conditional formatting to highlight a customer's birthday field on a form during their birth month. We will explore using the Month function and building expressions, highlighting any important month-specific records, without the need for additional queries or complex setups.
R Isaac from Cheney, Washington (a 10-year student) asks: I have developed an Access database for a local food bank. The director would like the birthday field for each customer in the registration form to be highlighted during their birth month. How can I code this? Should I build a query for the birth month or add conditional formatting to the birthday field to highlight it based on the current month?
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
Recommended Courses
Keywords
TechHelp Access, conditional formatting, month function, highlight birth month, expression in Access, Access formatting tricks, Access birthday highlight, date function, date value Access, design view Access, Access form design
Transcript
Today I'm going to show you how to highlight the birth date on a customer's form if it happens to fall within their birth month, for example. It's currently November, so you can see here two different records. This guy gets all highlighted. This way, when you open up the customer's record, you can quickly see, oh, it's your birthday - it's either just passed or it's soon, and I can say happy birthday.
Today's question comes from Isaac from Cheney, Washington. He's been a student of mine for 10 years and has his 10-year badge. He says, "I've developed an Access database for a local food bank. The director would like the birthday field for each customer in the registry form to be highlighted during their birth month. How can I code this? Should I build a query for the birth month, or use conditional formatting in the birthday field to highlight it based on the current month?"
You can do it without a special query field. That's one way to do it - you could make a query and then separate out the month right in its own field. But if you know a little trick with conditional formatting, which I'm going to show you in a minute, you can do it right in the customer form with no extra work.
Now, this will be an expert-level video. What does that mean? Well, expert to me is between beginner and developer. So it's a little bit more than the beginner levels, but it's not quite developer. We don't need any programming for this, but you've got to know some functions. What do you need to know? Well, you should obviously know what conditional formatting is. If you've never used it before, go watch this video first. We're going to use an option in conditional formatting called "expression is." So go watch this video if you want to get a head start on this, but I'm going to show you how to do it in just a minute.
And, of course, you're going to have to know how to figure out what month it is based on a date value. So we're going to use the month function. This video covers year, month, and day. Go watch these. These are all free videos. They're on my YouTube channel and my website. Go watch those and come on back.
Okay, here I am in my TechHelp free template. This is a free database. You can get a copy off my website if you want to, but you can do this in any database you want. I'm going to work with a single form here, but the same thing works whether you've got a single form or a continuous form. You can do the same thing.
All right, so what we're gonna do is we're gonna come in here, and we're gonna pretend "customer since" is "birthday." All right, I just changed the label over here. It's "customer since" though. Okay, you get it. So go to "customer since." We're gonna turn this thing off, go to "format," and then "conditional formatting." All right, new rule. Now, we're not gonna base it off the field value because we're not actually interested in the actual value - because the whole value involves a year, a day, and lots of stuff. We only want the month, so we have to split it out using the month function. But to do that, we're gonna have to go and make an expression.
Right now, what's the expression gonna be? It's going to be, is the month of "customer since" (the birthday, right) equal to the month of today's date? Okay, so we're gonna say month of your field "customer since." If that's equal to the month of today's date, then you have to be careful, and I'm gonna show you why in just a minute. This is what people usually type in, all right? If that's true, we'll set the background to yellow.
Okay. Now hit "okay." Everything looks fine, right? Looks good. Remember what this looks like, folks, right? Hit "okay" again. Save it, close it, reopen it, and it's not working. All right, even though it's currently November and this should be November.
Oh, I should probably tell you guys I'm using ISO dates. That's another thing. I'll put a link to my ISO date video down below. My goal is to get daylight savings time gone and everyone using ISO dates, but I digress. Here we go. Okay, so go back to design view now. Let's take a look at what the problem is here. Click on this guy again, go to "format," "conditional formatting," and look what happened. Do you see the subtle change there? Yeah, I'll show you what it used to be. All right, you used to look like this. This is what we had before, right? So we typed in, okay, we typed in month "customer since" equals month "date."
The problem is Access was trying to be helpful, and it thought, oh, you mean the word "customer since" and the word "date"? No, Access, no, thanks for trying, but you don't win a prize. Right? I want the field "customer since." And what do we have to do with fields? We have to put them inside of brackets. Even though we don't have spaces in our field names - which is something that I tell you never to do - sometimes these little wizards and these little tools try to be helpful, and they convert things for you. So you gotta put brackets manually around your fields in here. This is one of the things that always trips up new users.
Same thing with date: We don't want the word "date," we want the date function. So we have to put empty parentheses after that. This is where everyone always stumbles on this stuff. I did too, when I was a new user when I first started using Access. This kind of stuff is frustrating, all right, because you think it's supposed to work a certain way, and it doesn't. So double click on it, go back into here, "customer since" has to have square brackets around it, okay? Just one of those things you gotta remember. And date has to be the date function with empty parentheses after it, just like that.
Okay, and then once you got it like that, hit "okay." Hit "okay," save it, close it, open it, boom, there you go, and now it works. Same, and someone else, there it is. Do that, and you can do the same thing in a continuous form if you want. There's a customer list, the same thing here. Design view, open this up, format, conditional formatting, new rule, expression is the month of "customer since," the field "customer since," equals the month of today's date. Hit "okay," apply, save it, close it, open it. There's all your people who have birthdays this month.
Right, pretty easy stuff, right? Once you know the tricks. It's the tricks that always throw people. In fact, I remember myself years and years ago when I was first learning this stuff, I believe I did it the old way where you take a query, and you put a field in the query that's got the month separated in it, then you use that to - it was a pain. But once you know how this works, then that's not that hard.
If you want to learn more about this conditional formatting stuff, including advanced conditional expressions, I cover it in my Access Expert Level 19 class. Like I said before, it's a little bit beyond the basics, but it's good stuff. This class also covers a lot of other things like exporting, importing, Microsoft Word Mail Merge. You can see everything that's covered, there's lots and lots of stuff. I show you how to highlight a whole line. That's pretty cool. We do these little bars and all kinds of tricks. So Access Expert 19, you'll find a link to it down below.
So Isaac, I hope that answers your question. That's 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.
TOPICS:
Highlighting birth dates in Access forms
Using conditional formatting in Access
Month function usage in conditional formatting
Creating expressions in conditional formatting
Troubleshooting conditional formatting expressions
Using brackets for field names in Access
Using date function with parentheses in Access
Applying conditional formatting to single and continuous forms
COMMERCIAL:
In today's video, we're going to learn how to highlight a customer's birth date on their form when it falls within their birth month. You'll see step-by-step how to use conditional formatting to spot those birthdays without creating a special query. We'll talk about some handy tricks with the 'month' function and watch out for those pesky little details like bracket usage that can trip you up. You'll also get a glimpse into more complex conditional expressions from my Access Expert Level 19 class. It's all about making your database work smarter for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main goal of the video tutorial?
A. To teach how to create a query to filter records by birth month
B. To demonstrate how to use conditional formatting to highlight a customer's birth date during their birth month
C. To show how to import customer data into Access
D. To explain how to create detailed reports in Access
Q2. What specific Access function is used in the tutorial to determine the month of a date?
A. Year function
B. Day function
C. Month function
D. DateAdd function
Q3. Why doesn't the initial attempt to use conditional formatting work in Access?
A. The logic was incorrect and needed more complex expressions
B. The field value was incorrectly set to check the day instead of the month
C. The required brackets for field names and parentheses for functions were missing
D. The conditional formatting was applied to the wrong field
Q4. What tool or feature in Access does the tutorial emphasize avoiding for this task?
A. Design view
B. Macros
C. Queries to separate the month
D. Form navigation buttons
Q5. What mistake do people commonly make when using conditional formatting expressions in Access, as explained in the video?
A. Using incorrect operators for comparison
B. Not applying styles that make changes visible
C. Keeping function names in lowercase
D. Forgetting brackets around field names and empty parentheses for functions
Q6. What other tip related to formatting does the video mention for users to be cautious about in Access?
A. Always use ISO date formats for consistency
B. Only use uppercase letters for field names
C. Disable all formatting changes by default
D. Use equal signs instead of comparison operators in expressions
Q7. Which Access class mentioned in the video provides more insights into conditional formatting?
A. Access Introduction 101
B. Access Form Basics 1
C. Access Expert Level 19
D. Access Intermediate Level 10
Answers: 1-B; 2-C; 3-C; 4-C; 5-D; 6-A; 7-C
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 teaches you how to highlight a birth date on a customer's form if it falls within their birth month. For example, if it's currently November, you can easily see which customers have birthdays in November, allowing you to quickly acknowledge the occasion.
A student named Isaac, who has been studying with me for a decade and has his 10-year badge, has a question. He developed an Access database for a local food bank, and the director wants the birthday field in the customer registry form to be highlighted during the customer's birth month. Isaac wonders if he should create a query for the birth month or use conditional formatting for the birthday field.
You can achieve this without creating a special query field. Creating a query is one method, where you separate the month into its own field, but I'll show you a trick using conditional formatting directly within the customer form. It's a more advanced-level technique, positioned between beginner and developer. While it doesn't require programming, it does require some knowledge of certain functions. You should be familiar with conditional formatting, particularly the "expression is" option, which we'll use here.
Additionally, you'll need to know how to determine the current month from a date value using the Month function. I have free instructional videos covering year, month, and day functions available on my YouTube channel and website to help you with this.
In my TechHelp free template, which you can download from my website, I'll demonstrate this technique. You can apply it to any database, whether you have a single form or a continuous form.
We'll start by treating the "customer since" field as the "birthday" field. I'll adjust the label accordingly. We'll navigate to the "customer since" field and access "format," then "conditional formatting." Choose "new rule," and we'll work with an expression because we're interested in the month, not the entire date value.
The expression checks if the month of "customer since" (the birthday) matches the current month. The month function will allow us to split out the month component. However, Access often tries to assist by interpreting static text rather than fields, so to prevent errors, we'll manually add brackets around the field names. Additionally, the "date" function needs parentheses to function correctly.
By ensuring the expression is formatted correctly with square brackets around field names and parentheses for the date function, we can successfully highlight birth dates occurring in the current month. This technique will work for both single and continuous forms, displaying all customers with upcoming birthdays conveniently.
Once you are familiar with these methods, it becomes an efficient process. I too followed the older method involving queries when I began learning, but understanding these tricks simplifies the task significantly.
If you're interested in exploring more advanced conditional expressions and formatting, my Access Expert Level 19 class covers these topics and others like Microsoft Word Mail Merge, exporting, and importing. It's packed with information beyond the basics.
I hope this answers your query, Isaac. 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
Highlighting birth dates in Access forms
Using conditional formatting in Access
Month function usage in conditional formatting
Creating expressions in conditional formatting
Troubleshooting conditional formatting expressions
Using brackets for field names in Access
Using date function with parentheses in Access
Applying conditional formatting to single and continuous forms
|
