Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 207
Get notifications when this page is updated

Intro

In lesson 207, we continue building the Microsoft Access task manager started in Access 206. I will show you how to create a query for the task list, filter tasks by date, and choose whether to hide completed tasks. We will set up recurring tasks so completed items automatically reset, and I will show you how to use conditions and the Immediate If function in queries and macros. You will also learn to add custom filter boxes to your forms for interactive filtering. This lesson builds on Access 206 and covers advanced query and form design topics essential for developing your database projects.

Transcript

Welcome to Microsoft Access 207 Intermediate Microsoft Access, brought to you by 599cd.com. I am your instructor, Richard Rost.

We have a lot planned for today's class. Today is kind of part two of a two-part mini series between Access 206 and 207. We are working on our task manager that we started building in 206.

We are going to build a query to base our task list off of, and that query will let us do a lot of cool stuff. We are going to choose whether or not to hide the completed tasks. We are going to show tasks by date, so you can say, "Show me tasks as of this particular date." We are going to get those recurring tasks working. Remember, when we click on a task to close it, if it's supposed to be a weekly recurring task, it will set it for next week.

We are going to learn how to do conditions in macros - if-then statements, basically. If this value, then do this; if that value, then do that. We are going to learn the immediate if function. It's a function you can use in queries to make queries also do if-then results. We will learn how to do filtering results in forms. Not just what I have shown you before with filtering, where you right-click and pick a filter; we are going to actually put our own filter boxes on our form. It's going to be cool.

This is going to be one of those lessons you are going to want to watch twice. Make sure you watch this one straight through, then do the examples.

We require Access 206. Again, you have to take Access 206 first, or you are not going to get this one. This is kind of a two-part series here. I am going to reiterate it for this lesson, because this lesson has some really complicated stuff in it. Sit back, watch it one time through. It will make a whole lot more sense once you have seen what I am trying to do. When you see the final product, you will go, "Oh, OK, that is what he is trying to do."

Kind of like when we learned combo boxes back in Access 101, 102, or 103, and I said, "Sit back, relax, watch me do the wizard first." Then you understood it. Now you are a combo box genius because you saw it a couple of times. You saw what I was trying to do. This lesson is the same way. I am going to do some really complicated query stuff. It will make a whole lot more sense the second time through.

The sample database is available on your hard drive or on the website. Again, I recommend you start with the 206 one and work from that, or, of course, build it yourself. You will get much more out of this if you do the examples yourself instead of using my database. But if your database is a train wreck, then you may want to start fresh.

Even if you have been doing the examples, when people have emailed me and said, "I have been following along with you, but my database just isn't as nice as yours," that's OK. As long as you are doing the examples, feel free to pick up from the pre-207 version and work from that one. The important thing is that you try to do it yourself first. Do not be unhappy if it is not pretty. That is not a problem. Just try to do it yourself first and see if you can get yours working.

Quiz

Q1. What is one of the main focuses of the Access 207 class?
A. Building a report for sales data
B. Enhancing the task manager started in Access 206
C. Creating mailing labels
D. Designing security features

Q2. What new feature will the query enable in the task manager?
A. Exporting reports to Excel
B. Hiding completed tasks
C. Integrating with Outlook
D. Importing JSON files

Q3. How will tasks be filtered or displayed according to the lesson?
A. By category only
B. By priority only
C. By date, such as showing tasks as of a particular date
D. Only incomplete tasks will be shown

Q4. What topic related to macros will be covered in this lesson?
A. Looping through records
B. Creating custom ribbon tabs
C. Conditions using if-then statements
D. Merging records automatically

Q5. What function will be introduced for use in queries to handle conditional logic?
A. DSum
B. IIf (Immediate If) function
C. Format
D. Switchboard

Q6. What form feature will be improved in this tutorial?
A. Auto-numbering fields
B. Subform linking
C. Adding user-created filter boxes to forms
D. Merging multiple forms into one

Q7. What prerequisite is strongly recommended before starting this class?
A. Access 201
B. Access 103
C. Access 206
D. Access 303

Q8. What advice is given for approaching this class session?
A. Skip the examples and just listen
B. Watch the lesson all the way through first, then do the examples
C. Read the documentation only
D. Memorize all function names before watching

Q9. What does Richard recommend if your database is having issues?
A. Only use his pre-built database
B. Restart the computer
C. Start fresh or use the pre-207 sample database if needed
D. Install new hardware

Q10. What is emphasized as the most important part of the learning process?
A. Making the database look professional
B. Doing the examples yourself, even if your database is not perfect
C. Memorizing code syntax
D. Relying only on the sample database

Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-C; 7-C; 8-B; 9-C; 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 video from Access Learning Zone is part two of a short series focusing on building a more advanced task manager in Microsoft Access. In this session, we will continue developing the project we began in Access 206.

The first thing we will tackle is creating a query that will serve as the foundation for our task list. Using this query, we will have more flexibility and control over how we display tasks. For example, you will be able to choose whether to display completed tasks or hide them. There will also be an option to view tasks based on a specific date, so if you want to see what needs to be done by a certain day, you can filter accordingly.

Next, we will address how to handle recurring tasks. If you have tasks that repeat, such as weekly assignments, we will make it so that when you mark one as complete, the system automatically sets up the next occurrence for you. This is a very practical feature, and I will show you how to implement the logic so your recurring tasks are always up to date.

We will also explore how to use conditions inside macros, essentially using if-then statements. This way, you can tell Access to perform one set of actions if a certain condition is met and a different set if it is not. Alongside this, I will introduce the Immediate If function, a powerful tool you can use in your queries to get if-then results directly in your data output.

Filtering results in forms is another important topic today. This goes beyond the standard right-click and filter method. I will show you how you can add filter boxes directly onto your forms, giving your users a more interactive experience when searching and sorting through tasks.

This lesson is full of complex concepts, so I highly suggest watching the video all the way through before trying the examples on your own. The content will make more sense once you see the final version of the database in action. Just as you might remember from learning combo boxes in the earlier courses, things often become clearer after you watch the process first and then try it out yourself.

To follow along, you will need to have completed Access 206. If you try to jump in without it, you will likely find yourself lost, as this lesson builds directly on that previous work. I want to make sure everyone understands that, because the skills here require a good foundation.

You can find a sample database on your hard drive or on my website. I recommend starting with your own work from 206 so you get hands-on practice, but if you are having trouble or your project has gotten a bit too tangled, feel free to use the pre-207 database as a starting point. The most important part is that you try the examples yourself before turning to my work. Remember, your database does not have to be perfect. The goal here is to understand the concepts and be able to apply them, not to have a perfectly polished database from the start.

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

Building a query for task list display
Hiding completed tasks using a query
Filtering tasks by specific date
Implementing recurring tasks functionality
Using conditions in macros (if-then statements)
Applying the immediate if function in queries
Adding custom filter boxes to forms
Filtering form results with custom controls