|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Conditional Formatting By Richard Rost 
5 years ago
Use Conditional Formatting to Color Code Tasks In this video, I will show you how to use conditional formatting in Microsoft Access to color code your task due dates. Tasks that are past due will show up red. Tasks due today will be yellow. Tasks not due yet (in the future) will be green. I'll also show you how to have the current field that has focus show up as bright yellow. Amina from Kingston, Ontario, Canada (a Gold Member) asks: I have a list of tasks that I keep in Microsoft Access. Is there a way that I can color code them red for overdue, yellow for due today, and green for due in the future? MembersMembers will learn how to use Expressions in conditional formatting to change the color of a field based on the value in another field. You can use this to change the color of an entire record (row) if you'd like.
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!
Links
Courses
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, conditional formatting, field has focus, equals, not equals, future, past, expression is, based on another field, Highlight data, How to Highlight Records or Values, apply conditional formatting, forms, reports, Conditionally Formatting, conditional formatting based on another field, greyed out, not working, based on date
IntroIn this video, we will talk about how to use conditional formatting in Microsoft Access to color code your task due dates so that overdue items appear in red, items due today show in yellow, and future tasks are green. I'll also show you how to highlight any field that currently has focus with a bright yellow background, and walk through practical examples using both single and continuous forms. You'll also learn tips to troubleshoot common issues with the conditional formatting feature in Access.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to talk about conditional formatting. I'm going to show you how to use conditional formatting in Microsoft Access to color code the dates that your tasks are due. Stuff that's due in the past will be red. Stuff that's due today will be yellow and stuff that's not due yet in the future will be green. Then I'll show you how to make whatever field has focus show up as bright yellow. That's all coming up. Today's question comes from Amina from Kingston, Ontario, up in Canada. One of my gold members. Amina says, I have a list of tasks that I keep in Microsoft Access. Is there a way that I can color code them? Red for overdue, yellow for due today, and green for due in the future. Yes, of course, Amina, we're going to use something called conditional formatting. Let me show you how that works. Here I am in my TechHelp free template. This is a free download on my website. If you want to go grab a copy, I'll put a link in the links section down below the video. You can go click on it. Let's start out with a simple example. Let's go to the customer form. Let's say in here, if the credit limit is over $1,000, I want this to show up in bright yellow. Right click, design view. Let's open up the ribbon up here. I'm going to click on credit limit. Conditional formatting is right there on the Format tab. Click on that. We're going to create a new rule. Now, format only cells where the field value is, I'm going to say greater than or equal to 1,000. What format do you want to set? Drop this down. Let's pick a yellow background. This is the preview over here. I know this looks confusing. That shows up in red. Don't change it and you'll be black. Hit OK, hit Apply, hit OK again. Now let's save our form, Control+S, close it, and then open it up again. All right, look at that. This one shows up with the yellow background. Let's go to the next record, 4,000. Zero. Notice the conditional formatting is gone. 500. Just these guys up front here. If I change one of these, let's change this to 2,500. There you go. See, the formatting kicks in. That's a real simple example, and that's fine on single forms if you want to draw attention to something. But where conditional formatting really shines is on a continuous form where you have a bunch of records showing up at once. If you don't know how to make these, by the way, go watch the videos where I build this TechHelp free template. I cover all the semifrugular classes too. Let's do Amina's example. Let's pretend I've got customers in here. Let's pretend that these are tasks. This is the due date for the task. Anything in the past, anything that was due in the past, we'll make red. Stuff that's due today will be yellow. Stuff that's due in the future will be green. You can assign multiple rules to each field. Right click, design view, format, click on customer since. That was grayed out because I wasn't clicked on it. If you can't conditional format whatever is selected, like the form itself or the section, that will be grayed out. You have to click on a field. There we go. Conditional formatting. New rule. Field value is less than and then today's date. Now, I'm not going to type today's date in. I'm going to use the date function. It's Date() with open and close parentheses. That's the date function. Down here drop this down. I'm going to pick red, like this washed out red right here. Let's go with that one. I don't want the big bright red. Hit OK. Hit OK. Save, Control+S. Let's close that and let's open it up. There we go. The ones that are in the past show up as red. I changed some of the dates. They were all in the past before because this was customer since, so I cheated a little bit. Got a couple of future ones, got today. Today's 10-21. You can quickly and easily look at the list here and see which ones are past due. Let's do another one. Let's do today's date. Ones that are from today, that are due today, show up as yellow. Click on customer since again. Format. Conditional formatting. New rule. Field value is equal to today's date. The date function. Let's make it yellow. I'll pick that yellow. Hit OK. Hit OK. Save it. Close it. Open it up and wait a minute, it's not working. What did we do wrong? This should be yellow. Let's go back in and take a peek. Right click. Design view. Click on it. Format. Conditional formatting. Oh, look. These are different. What happened? Now, I did this intentionally to teach you. If you forget that open and close parentheses after the word Date, Access doesn't use the date function. It puts the actual word Date in there inside quotes. That's a text string. You have to make sure if using Date or Now or any other function, you put the empty parentheses after it. Let's double click on this guy to edit it. Get rid of the quotes and then put open and close parentheses after it. Hit OK. Hit OK. Save it. Close it. Boom. There you go. One more. Right click. Design view. Click on it. Format. Conditional formatting. New rule. Field value is greater than today's date. Let's make that green. Maybe like that guy. See? There they are. You can rearrange these, by the way, if you want to change the order, you can use the buttons right here. But these shouldn't overlap, so it doesn't matter. Hit OK. Save it. Close it. Open it up. There we go. See that? I'm going to show you one more trick with conditional formatting. I'm going to show you how you can have any field that is selected, where the cursor is blinking, show up as bright yellow. How do we do that? First, a quick word from our sponsor. That's me. If you want to learn more about conditional formatting, I cover it in detail in Access Expert Level 19. You can find my website. I'll put a link in the link section down below the video. I spent a lot of time on conditional formatting covering all the different kinds of rules. Field expressions. The thing I'm going to show you in a second: which field has focus. That's kind of a cool rule. You're going to see how this works in just a second. You can do data bars. There's all kinds of neat stuff you can do. You can see all that in Access Expert Level 19. Back to our regularly scheduled program. Say that ten times fast. Right click. Design view. Now, click on first name or any field. Format. Conditional formatting. New rule. Field has focus is the one you're looking for. That means the cursor is currently sitting on that field. Set whatever format you want. Hit OK. Hit OK. Now that's just the first name field. Save it. Close it. Come back in. Now if I click on any first name, look at this. It goes yellow. You can do it to any of the fields you want. Let's do it to last name. Right click. Design view. In fact, I'm going to do last name, state, credit limit, and this guy altogether. Because this guy, customer since, has specific rules in it already, so we don't want to overwrite those. If I just do another one now for a multiple selection, it will overwrite whatever is in there. Format. Conditional formatting. Same thing. New rule. Field has focus. Go yellow. Hit OK. Hit OK. Save it. Close it. Open it up. Now anywhere I click in any of those fields, you can see how it goes yellow. The only exception right now is customer since. Since we already had formatting in there, we have to add that as a separate condition. So customer since. Format. Conditional formatting. You don't want to lose these guys. New rule. Field has focus. Drop that down and go yellow. Hit OK. Now it is the fourth one on there. Pick notice that it's last in the order. Hit OK. Open it up. Look, it's not working. Why isn't it working? That's because one of these guys is going first and then it stops. If you want the field has focus to take precedence, that's what that order is for. Click on that, and use this button to move it up to the top. As soon as it hits one of these rules that works, it stops. I know Excel has a little checkbox over here that you can click on where you can say stop if this rule applies. Access doesn't have that. Hopefully in a future version they will add it. Microsoft, stop giving Excel cool features that Access doesn't have. Hit OK. Save it again. Now field has focus will take priority. See that? But the other ones will still run. That's kind of neat. I like that. Conditional formatting is really cool. You can do all kinds of good stuff with it. I just scratched the surface in this video. I spend lots and lots of time on conditional formatting. If you want to learn more about conditional formatting, in the extended cut for members, I will show you how to use expressions. That's where you can have conditional formatting of a particular field based on the value in a different field. As you can see here, first name, last name, state, and credit limit are all taking the conditional formatting that customer since has, so the whole row apparently has the same format. That's all covered in the extended cut. All the other members and up get access to all the extended cut videos. Gold members can download these databases. How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series. Platinum members get all the previous perks plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you are 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. Don't worry, 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. QuizQ1. What is conditional formatting primarily used for in Microsoft Access as described in the video?A. To generate reports automatically B. To color code fields based on certain conditions C. To backup data to the cloud D. To create data entry forms Q2. In the video, what colors are suggested for tasks due in the past, today, and in the future? A. Red for today, yellow for past, green for future B. Green for past, yellow for today, red for future C. Red for past, yellow for today, green for future D. Yellow for past, red for today, green for future Q3. Where is the Conditional Formatting button located in the Access form design view? A. Data tab B. Home tab C. Format tab D. External Data tab Q4. What do you need to remember when using the Date function in conditional formatting rules in Access? A. Type the date in quotes B. Always capitalize DATE C. Add open and close parentheses after Date D. Use Date without any parentheses or quotes Q5. What will happen if you omit the parentheses when using Date in a conditional formatting rule? A. It will use the system clock time B. Access will display an error message C. It will treat Date as a text string, not as the function D. It will insert today's date automatically anyway Q6. How can you create multiple conditional formatting rules for a field? A. You cannot create multiple rules for the same field B. Assign one rule per record only C. Add a new rule for each condition in the Conditional Formatting wizard D. Only by using VBA code Q7. What does the "Field has focus" rule do in conditional formatting? A. Highlights the entire record B. Highlights the field currently selected by the user C. Changes the color of the form background D. Sorts records automatically Q8. If you want the "Field has focus" rule to take priority over the other rules, what must you do? A. Delete all other rules B. Move "Field has focus" to the top of the rule order list C. Archive old records D. Access does this automatically Q9. What happens if you assign conditional formatting rules to multiple fields at once, but one of the fields already has specific rules? A. The previous rules are combined with the new ones B. The specific rules are overwritten if not done carefully C. All formatting is removed from that field D. There can never be a conflict between rules Q10. In access conditional formatting, does Access have a "stop if this rule applies" setting like Excel? A. Yes, it is available in the same dialog box B. No, Access does not have this feature C. Only Access 365 has this feature D. It is available through an add-in only Q11. What benefit do Gold members get that Silver members do not, according to the video? A. The ability to ask TechHelp questions B. Access to all beginner courses C. Downloading sample databases and code vault D. Access to Word and Excel tutorials Q12. When would you use expressions in conditional formatting as explained in the extended cut? A. Only when coloring every record the same way B. To format a field based on values in another field C. Only to show the current date in red D. To set fixed colors for all fields Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-B; 11-C; 12-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. SummaryToday's video from Access Learning Zone focuses on using conditional formatting in Microsoft Access to improve your task management forms. I want to show you how you can color code the due dates for your tasks so it's easy to see which ones are overdue, which are due today, and which are upcoming in the future. Tasks past due will appear in red, tasks due today will be yellow, and upcoming tasks will be green. Additionally, I will show you how to make the currently selected field stand out in bright yellow so it's easier to keep track of where you're working.Today's topic comes from a question by someone who wants to know if it's possible to color code a list of tasks in Access with red for overdue, yellow for today, and green for tasks due in the future. The good news is this is definitely possible using a feature called conditional formatting. To start, I'll demonstrate a basic example. Using my free TechHelp template available on my website, I'll open up the customer form and suppose I want any credit limit over $1,000 to highlight in bright yellow. By switching to design view, selecting the credit limit field, and opening conditional formatting from the Format tab, I can create a rule that changes the background color to yellow when the value is 1,000 or above. Once that's set up, saving and reopening the form shows the formatting in action. This is a straightforward use of conditional formatting that's helpful for single forms, but where the feature really shines is in continuous forms where you can easily distinguish between many different records at a glance. For a more advanced example, similar to our original question about tasks, let's treat the Customer Since date as a due date for demonstration. I want any dates in the past to show in red, those due today in yellow, and those in the future in green. You can stack multiple formatting rules on a single field in Access. First, add a rule for past dates by setting the conditional format to use the Date() function, which always returns today's date. Set this rule to mark all dates before today in red. Next, create a second rule for dates equal to today with a yellow highlight. It's important to use the Date() function with empty parentheses. Sometimes, if you forget those, Access will interpret it as a text string rather than a date, and your formatting won't work as expected. If that happens, simply edit the rule and add in the parentheses. Then, add a third rule for future dates, setting those to display in green. Conditional formatting allows you to rearrange your rules, which can matter if there is overlap. In our example, each category (past, present, future) is exclusive, so the order will not affect the results. I also want to demonstrate a useful trick with conditional formatting: highlighting whichever field has the current focus by changing its background color to a bright yellow. To apply this, select the field, open conditional formatting, and choose the rule that formats the field when it has focus. You can apply this rule to any or all fields as needed. For fields where you already have other conditional formatting (for example, those using the date color coding), you will need to add the "field has focus" rule after the others. Remember, the order in which formatting rules are listed is important. Move the "field has focus" rule to the top if you want it to override the others when active. Conditional formatting in Access is a powerful feature and I highly recommend getting familiar with all of its options. I only covered the basics here. There is much more you can do, such as using expressions to base formatting on the values of other fields, or creating data bars for visualization. All of these are discussed in greater detail in my Access Expert Level 19 class. For those interested in learning even more, in the extended cut for members I explain how to use expressions so that the formatting of one field can depend on the values of a different field. I also cover scenarios where all fields in a row seem to take on the formatting for the date field, which can be useful or confusing, depending on your needs. If you'd like access to all of these extended cut videos, along with other perks such as downloadable sample databases and exclusive classes, you can join as a member. Silver members receive access to TechHelp extended cut videos and monthly beginner classes. Gold members can download all sample databases and gain access to the code vault, as well as get priority for support. Platinum members receive all previous benefits plus access to my full collection of beginner courses for all subjects and monthly developer classes. Membership benefits are clearly outlined if you decide to join. Of course, all these TechHelp videos will continue to be freely available to everyone. As long as you keep watching, I'll keep producing more content to help you build your Access skills. 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 ListApplying conditional formatting in Access formsColor coding overdue, due today, and future due dates Using conditional formatting rules with date values Highlighting fields with focus using conditional formatting Using the Date() function in conditional formatting Correcting errors with function usage in rules Setting rule precedence in conditional formatting Applying conditional formatting to multiple fields Understanding conditional formatting order and priority |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access conditional formatting, field has focus, equals, not equals, future, past, expression is, based on another field, Highlight data, How to Highlight Records or Values, apply conditional formatting, forms, reports, Conditionally Formatting, c PermaLink Conditional Formatting in Microsoft Access |
