Next Business Day
By Richard Rost
3 years ago
How to Find the Next Business Day in MS Access
In this Microsoft Access tutorial I'm going to teach you how to calculate the next business day following any date in your queries. Then we'll also use the same logic to calculate the previous business day.
Aimee from El Paso, Texas (a Platinum Member) asks: I need to be able to calculate the next business day following an order date. For example if the order comes in on a Saturday I need the following Monday. How can I do this?
Members
Members will learn how to create a VBA function to calculate next and previous business day so that function can be used anywhere in the database including forms and reports.
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!
Pre-Requisites
Links
The Code
- D: CustomerSince
- WD: Weekday(D)
- WDN: WeekdayName(WD)
- NextBizDay: IIf(Weekday([D])=6,[D]+3,IIf(Weekday([D])=7,[D]+2,[D]+1))
- NWD: Weekday([NextBizDay])
- NWDN: WeekdayName([NWD])
- PrevBizDay: IIf(Weekday([D])=2,[D]-3,IIf(Weekday([D])=1,[D]-2,[D]-1))
- PWD: Weekday([PrevBizDay])
- PWDN: WeekdayName([PWD])
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, next business day, previous business day, next biz day, prev biz day, nextbizday, prevbizday
Intro In this video, I will show you how to calculate the next or previous business day from any given date in your Microsoft Access queries. We will talk about using calculated fields, aliases, the IIf function, and the Weekday and WeekdayName functions to identify and work with business days. I'll walk you through building the logic for handling weekends and discuss how you can use this in queries, forms, and reports. This video does not cover handling holidays or custom days off.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I am your instructor Richard Rost.
In today's video, I'm going to show you how to find the next or previous business day from any date you specify in your Microsoft Access queries. This is going to be what I call an expert level video, which is between beginner and developer. That means it's beyond beginner, but it's not quite into VB programming. There are some functions involved, and I'll show you what you're going to need to know first in just a second.
Today's question comes from Amy in El Paso, Texas, one of my platinum members. Amy says, I need to be able to calculate the next business day following an order date. For example, if the order comes in on a Saturday, I need the following Monday. How can I do this?
I was talking to Amy in email and she said they take in orders and they guarantee a next business day delivery. If an order comes in on a Sunday, for example, it gets delivered on Monday. If it comes in on a Friday, it gets delivered on Monday. So in all those cases, she has to see how to figure out the next business day. Using the same logic, we're also going to calculate the previous business day. It works just the same way.
Since this is an expert level class, it's a little bit beyond beginner. Here are some things you should know. You should know how to make calculated fields in queries. You should know what an alias is. We'll be using the if function, the immediate if function. It's basically an if-then statement inside of a function. We're going to be using the weekday function to determine what day of the week we're dealing with. Sunday is one. Monday is two, and so on.
Finally, go watch my date math video. I explain how dates and times work inside of Access. You can get around using a lot of the advanced functions like dateadd if you simply know that you can add one to a date and that's tomorrow. Go watch this too.
All of these are free videos. They're on my website. They're on my YouTube channel. Go watch those now if any of that stuff doesn't seem familiar. Go watch those and then come on back.
So here I am in my TechHelp free template. This is a free database. You can download a copy from my website. In here, I've got a customer table. In this customer table, I've got a date field here called customer since. We'll use that as our date field.
Let's go make a query. We're in design. I'm going to bring in that customer table. Close that. For this, all we need is the customer since field. That's our date. I'm going to alias that. I'm going to call that D. So it's D: customer since. If I run the query now, it looks just like that. It's a list of dates. They're all over the place. That's okay.
The first thing we need to determine is what day of the week that is. Next field over here, and I'll zoom in with Shift+F2 just to show you what I'm doing a little bit better here. We'll call this WD. That'll be the weekday of D, my customer since field. That's going to give me a number from 1 to 7, indicating the day of the week.
Let's change this first one. I'll put today's date in there. I'm going to hit Control+semicolon. That puts today's date in there: 2023-04-06. It's April 6, 2023. Today is a Thursday. Thursday is number 5. Want to see the day of the week over here? Let's go to the next field. We're going to say WDN is weekday name of WD. That takes that number 1 through 7 and feeds it into the weekday name function, which we also learned about in the weekday video. It creates a new field called WDN. Now we can see what the day of the week is. We don't really need that. It's just nice to see.
What's next? Next, we have to figure out how to calculate what the next business day is. I'm going to bring up Notepad here and write this out in English. If today is Friday--and Friday is going to be what? A six. One is Sunday, Monday, Tuesday, Wednesday, Thursday, Friday. A six. So if today is Friday, then the next business day is going to be three days after that. Saturday, Sunday, Monday. Add three.
Otherwise, if today is Saturday, that's going to be a seven, then add two. Otherwise, just add one for all the rest of the days. We can accomplish this with a nested if function--that's two if functions together. Here's what it's going to look like. I'm going to move this off to the side now.
Let's go back to design view. Look on the next field. Shift+F2 to zoom in. Let's call the field next biz day: Next Business Day. If the weekday of D equals 6, then next business day is going to be D plus three. Otherwise, here comes your other if statement inside here. If weekday of D equals 7, then that's the comma, we're going to add two to it, D plus two. Otherwise, it's just going to be D plus one.
Starting with the innermost if. If the weekday of D is 7, if it's a Saturday, add two to it, otherwise add one to it. Take this whole thing and put that inside of this if statement, saying if the weekday is a Friday, add three. Otherwise, look at this function. That's how nested if functions work. Then we'll hit OK and let's give it a shot.
This is Thursday, so that's the next day. Saturday, that's two days later. It looks like it's looking good. Friday--let's put the weekday name in the next column over here.
Let's call it NWD for next weekday. It's going to be the weekday. I always want to type in weed. You'd think something was going on here. No, I'm not a weed smoker. Weekday of the next business day. Take this value we just calculated and give me the weekday of that. Now there we go.
Now, we can take it one step further. You can say NWDN. Yeah, these are kind of confusing. It's going to be the weekday name of the D. Anything that I plan on actually using in a form or a report, I give those good names. Like, I'm going to use next business day. I don't like to abbreviate things I'm actually going to use in other places, but these pretty much are just for inside this query.
There we go. I'm going to use the next business day. So there are Thursday to Friday, Saturday to Monday, Sunday to Monday, Friday to Monday, and so on. It looks like it's working just fine.
That's basically, in a nutshell, how you do it. Now, previous business day just goes backwards with the same kind of logic. Take a minute now. If you want to really learn this stuff, the best way to learn it is to struggle a little bit and try to figure this out on your own. Pause the video, and using the same technique I just did, try to go through and do the previous business day function. Pause it now and I'll wait for you.
Previous business day, prev biz day or whatever you want to call it. If today's weekday of the date, if the weekday equals two--remember, Sunday is one, Monday is two--then we're going to set it to D minus three. It's going to back it up to Friday. That's the previous business day. Otherwise, if weekday of D is a one, if it's Sunday, go D minus two. Otherwise, it's just D minus one.
Run it. Looks good. The 6 goes back to the 4th. Then we'll put the other stuff in there that we had. Let me make these a little bit smaller. This is going to be PwD, not a password, is going to be the weekday of previous business day. Then PwDN name is going to be the weekday name of PwD.
Now, final shot. There we go. Today's Thursday, next business day is Friday, previous business day is Wednesday. Today's a Sunday, goes back to Friday. If today is a Monday, it goes back to Friday. Looks like it's working. You should never see a Saturday or Sunday in here.
Now that you have it in a query, you can use this anywhere you want to. If you want to use this in a form, all you have to do is put this in whatever query is behind that form or base your form on this query and you can use these fields if you want to.
Keep in mind, this only takes into consideration weekdays. It doesn't take into consideration anything on your calendar like holidays or days off or stuff like that. If you want to have it not calculate, for example, New Year's Day, bank holidays, then you'll need a more advanced function.
I have another video called Workdays that calculates the number of workdays between two dates. Similar to this, the video that we just looked at only calculates the next business day or the previous business day. Workdays will actually count the number of days between two dates.
In the extended cut for this video, I do show you how to exclude holidays and company specific days off. We basically create a table that's got those dates and it's got floating holidays, and we also have static holidays, things that you know are always on the same date. For example, Christmas is always on December 25th. The extended cut video for the members talks about how to do that. I'll put a link to this video down below.
What I showed you here only works in a query. Yes, you can use this query function in a form or report if you base it on this query. But what if you want to put this in a function so you can use it anywhere else in the database you want to? That's what we're going to cover in the extended cut for the members. We're going to make a VBA function out of it and then we'll be able to just slap it right into any field that we want to.
Members get access to all of my extended cut videos. There are lots of them--like 300 and some of them now--so there's plenty of stuff to watch. Gold members can download these databases and you get access to my code vault and free lessons and lots more.
One more thing--one little segment that I like to add to some of my videos now is how does ChatGPT handle this? I asked ChatGPT how to do this and it came up with a pretty good response. I said, how can I calculate the next business day following a date D in a Microsoft Access query and it came up with something. It used the DateAdd function which technically will work just fine. But, if you know date math in Access, you don't need a special function for that. You just have to add the number of days. But it looks like it's got the same logic. If the weekday equals 6 then it adds three. I see what it did here. It's thinking that D is 6 is Saturday and D of 7 is Sunday, so it's got the numbers wrong because in Microsoft Access the default Sunday is one. You can actually change this. There's a parameter to change that. You go to Microsoft site, look up the syntax for weekday. There's an optional second parameter and you can say the first day of the week is a different day of the week. With this you'd have to use the optional parameter to say that the start of the week is Monday instead of Sunday. In this particular case, exactly what it's got here, ChatGPT actually fails, but it's close enough for me to give it a passing grade because it would have given you the correct answer if you had told it that. So, we'll let it slide.
The best thing about ChatGPT is that the first attempt that it gives you isn't always correct, but it will learn from its mistakes. If you say, okay, that's the issue, that it's not the first day of the week, it will come back usually with the correct answer. Try it yourself, plug it in, see what you get.
So, that is your TechHelp for today. That's your fast tip. That's your TechHelp video for today. What's the difference? I don't know. Everyone always asks me that. TechHelps are usually a little more involved and involve a question from one of my students, whereas fast tips are usually just things I want to throw out there quickly. Sometimes the fast tips have a question, sometimes the TechHelps. It's whatever I feel like doing. I'm probably going to merge them together at some point.
I hope you learned something today. Live long and prosper, my friends, and I'll see you next time.Quiz Q1. What is the main goal of the video tutorial?
A. To teach how to create tables in Microsoft Access
B. To explain how to calculate the next or previous business day in Access queries
C. To show how to design a user interface for Access
D. To demonstrate how to import data from Excel into Access
Q2. Which Microsoft Access function is primarily used to determine the day of the week for a given date in this lesson?
A. DateAdd
B. Weekday
C. DateDiff
D. Now
Q3. In Microsoft Access, what value does the Weekday function return for Sunday by default?
A. 6
B. 7
C. 1
D. 2
Q4. According to the logic in the video, if an order is placed on a Friday, how many days should be added to determine the next business day?
A. 1
B. 2
C. 0
D. 3
Q5. What is the main purpose of using nested if (immediate if) statements in calculating the next business day?
A. To simplify query syntax for beginners
B. To handle different conditions for weekends and weekdays
C. To validate user input
D. To automatically format date fields
Q6. What does the tutorial suggest you should already know to follow along with the expert-level material?
A. How to create custom VBA modules
B. How to make calculated fields and use aliases in queries
C. How to link multiple tables in reports
D. How to configure database security settings
Q7. Which of these is NOT considered in the basic solution shown in the video?
A. Weekends (Saturday and Sunday)
B. Standard holidays (like New Year's Day)
C. Determining if a date is Monday through Friday
D. Calculating the weekday number
Q8. How does the logic adjust if the given date is on a Saturday?
A. Add one day
B. Add three days
C. Add two days
D. Subtract one day
Q9. For calculating the previous business day, what happens if the original date is a Monday?
A. Subtract three days to reach Friday
B. Subtract two days to reach Saturday
C. Subtract one day to reach Sunday
D. Subtract four days to reach Thursday
Q10. According to the video, what is a significant limitation of the solution as originally shown?
A. It cannot be used in queries
B. It only works on forms, not in reports
C. It does not exclude holidays or custom days off
D. It cannot be customized for different business rules
Q11. What extended feature does the member-only video cover?
A. Creating a table relationship diagram
B. Making a VBA function to use the logic anywhere in the database
C. Automating exports to Excel
D. Building dynamic charts in reports
Q12. When calculating business days, which aspect does the basic Access query NOT take into account that might require more advanced logic?
A. Leap years
B. Holidays that may fall on weekdays
C. Weekends
D. Time zones
Q13. What common mistake did ChatGPT make in its suggested solution for this task?
A. It used the wrong date formatting
B. It assumed Sunday was weekday number 6 instead of 1
C. It incorrectly calculated the number of business days between two dates
D. It generated a syntax error incompatible with Access
Q14. What feature allows you to change which day of the week is considered the first in Access's Weekday function?
A. An optional second parameter in Weekday()
B. The DefaultDay property in the Table Designer
C. A custom function you must create
D. The Calendar Settings in Control Panel
Q15. If you want to use the next or previous business day calculation in a form or report, what should you do?
A. Use the formula only in VBA modules
B. Base the form or report on the query containing the calculation
C. Re-enter the calculation manually each time
D. Export the data to Excel first
Answers: 1-B; 2-B; 3-C; 4-D; 5-B; 6-B; 7-B; 8-C; 9-A; 10-C; 11-B; 12-B; 13-B; 14-A; 15-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 focuses on finding the next or previous business day for any date in your Microsoft Access queries. I'm Richard Rost, your instructor, and in this expert-level lesson, I will take you through the steps needed to solve this common business problem. This lesson is a bit above beginner level, but it does not require VBA programming experience. You will need to be comfortable working with calculated fields in queries, know what an alias is, and be familiar with some basic Access functions like Immediate If (IIf) and Weekday. If these concepts sound new to you, I recommend brushing up on those topics first. I have free introductory videos on my website and YouTube channel covering calculated fields, aliases, and working with date and time values, so be sure to review those if you are not confident with these skills.
The question for today comes from a student who needs to calculate the next business day after an order date, for instance, ensuring that if an order is placed on a weekend, the next business day would be the upcoming Monday. We will also cover how to determine the previous business day using the same logic, just traveling backwards through the week instead of forwards.
For demonstration, I built a small example using my free TechHelp template database, specifically working with a field called "customer since" in the customer table. To start, create a query that references your date field and use an alias for clarity; for example, I used D as an alias for the "customer since" date. Next, it is important to determine what day of the week each date falls on, and you can do this by adding a calculated field that uses the Weekday function. Sunday is represented by 1, Monday by 2, all the way up to Saturday as 7.
For extra visibility, you can use the WeekdayName function to display the day in text format for any date field. While this step is not essential to the calculations, it does provide a nice reference for verifying that your logic is correct.
Now, the heart of the lesson is figuring out how many days to add to a given date to find the next business day. The rule is as follows: if the current day is Friday (weekday equals 6), you need to add three days to reach Monday. If it is Saturday (weekday equals 7), add two days. For all other days, simply add one day. This logic can be implemented using nested Immediate If (IIf) functions in your query. The inner function handles the Saturday case, and the outer function handles Friday. Any other day moves ahead by one.
You should then confirm your results by adding related fields to show the weekday and its name for each calculated next business day. With this setup, your query will correctly identify the next business day for any given date, whether that date falls on a weekday or over the weekend.
To find the previous business day, the process is similar but works in reverse. If the date falls on a Monday (weekday equals 2), you subtract three days to arrive at Friday. If it is a Sunday (weekday equals 1), subtract two days. Otherwise, go back one day. This way, your logic ensures that Saturdays and Sundays are properly skipped when calculating both forward and backward.
After verifying your calculations, you will see that your output never assigns a business day to a Saturday or Sunday, as intended. You can now use these query calculations in forms or reports simply by basing those objects on your query.
It is important to note that this approach takes into account only regular weekdays and does not consider holidays or company-specific days off. If you need to exclude certain days such as Christmas or company holidays, you will need a more advanced solution. I have a separate video on calculating workdays between two dates, which introduces holiday exclusion. In the extended cut for today's tutorial, we will build on this logic to exclude holidays and special days off by creating a table of such dates and demonstrating how to use both floating and fixed holidays in your calculations. I will also show how to convert this query logic into a reusable VBA function so you can apply it throughout your database.
For those interested in what AI tools can do for this problem, I also looked at how ChatGPT suggests calculating the next business day. Its solution largely matches the logic I've described, but you need to be careful with the way it handles days of the week; Access defaults to Sunday as 1 unless you specify otherwise. If you clarify how your week is structured, ChatGPT's answer can be adjusted to work as required.
To sum up, TechHelp videos like this one typically answer questions directly from students and go into more detail than my Fast Tips, which are usually quick bits of advice or shortcuts. I hope this lesson has given you the knowledge to implement business day calculations in your Access queries.
For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Calculating the next business day in Access queries
Calculating the previous business day in Access queries
Using calculated fields in Access queries
Assigning aliases to fields in queries
Using the IIf (immediate if) function in queries
Using the Weekday function to determine day of week
Displaying weekday names using the WeekdayName function
Handling Friday and Saturday input for next business day logic
Handling Monday and Sunday input for previous business day logic
Building nested IIf statements for conditional date logic
Applying date math by adding or subtracting days
Utilizing calculated business day fields in forms and reports
Limitations of logic regarding holidays and special days |
