0:00:00
Welcome to another Tech Help 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. So there are some functions involved and I'll show you what you're going 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.

0:00:43
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 yeah they take in orders and they guarantee a next business day delivery. So 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. And using the same logic, we're also going to calculate the previous business day. It works just the same way. All right, so since this is an expert level class, it's a little bit beyond beginner, so here's some things you should know.

0:01:16
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, right? That's important, all right? Sunday is one, Monday is two, and so on. And finally go watch my date math video I explain how dates and times work inside of Access. You can just get around using a lot of the advanced functions like date add and stuff like that if you simply know that you can add one to a date and that's tomorrow. So go watch this too. Alright all of these are free videos. They're on my website. They're on my YouTube channel go watch those now any of that stuff that you don't seem familiar with well Go watch those and then come on back. I'll wait for you Okay, so here. I am in my tech help free template. This is a free database You can download a copy up my website and in here. I've got a customer table and in this customer table I've got a date field here called customer sense. We'll use that as our date field all right, so let's go make a query create Create, Query Design, I'm gonna bring in that customer table, all right, close that, and for this, all we need is the CustomerSense field, that's our date, and I'm gonna alias that, I'm gonna call that D, all right, so it's D colon CustomerSense.

0:02:40
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. Now, the first thing we need to determine is what day of the week is that. So let's come in here, our 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 sense field. And that's going to give me a number from one to seven indicating the day of the week. Let's change this first one, I'll put today's date in there.

0:03:12
I'm going to hit control, semi-colon, and that puts today's date in there 2023 0406 it's April 6th, 2023 and Today is a Thursday. Thursday is number five Want to see the day of the week over here? Well, let's go into here and let's go to the next field and we're gonna say WDN is week day week day name of WD. That takes that number one through seven, feeds it into the weekday name function, which we also learned about in the weekday video, and creates a new field called WDN, and now we can see what the day of the week is.

0:03:51
We don't really need that, it's just nice to see. Okay, any questions so far? Raise your hand. I'm just kidding, I'm just kidding. All right, what's next? All right, next we got a little logic. We gotta figure out how to calculate what the next business day is. I'm gonna bring up Notepad here and write this out in English, okay?

0:04:09
If today is Friday, and Friday is going to be what? A six, right? One is Sunday, Monday, Tuesday, Wednesday, Thursday, Friday is six. So if today is Friday, then the next business day is gonna be three days after that, right? Saturday, Sunday, Monday, then add three. Okay. Otherwise, if today is Saturday, that's going to be a seven, then add two, right? Otherwise just add one for all the rest of the days.

0:04:43
Okay. So we can accomplish this with a nested if function that's two if functions together. And here's what it's going to look like. I'm going to move this off to the side now. All right, let's go back to design view in here. Click on the next field, shift F2, zoom in. All right, let's call the field next biz day colon. D equals 6, then next business day is going to be D plus 3. Otherwise, here comes your other if statement inside here. If weekday of D equals 7, then that's the comma, right?

0:05:32
We're going to add 2 to it, d plus 2. Otherwise, it's just going to be d plus 1. Okay? So starting with the innermost if, right? If the weekday of d is 7, if it's a Saturday, add 2 to it. Otherwise, add 1 to it. All right? And take this whole thing and put that inside of this if statement, saying if the weekday is a Friday, add 3. Otherwise, look at this function that's a nested if functions work and then we'll head okay and let's give it a shot okay it looks it's and just looking at this year this is thursday so that's the next day right saturday that's two days later it looks like it's looking good friday let's put the weekday name in the next call here so come over here. All right. Next we're going to have, uh, let's call it NWD for next week. Day is going to be the week. Day.

0:06:34
I always want to type in weed. He'd think something was going on here. No, no, I'm not a weed smoker. Week, week day of the next biz day. All right. Take this value. We just calculated and give me the weekday of that. And now, there we go, there's that. And now we can take it one step further, and guess what, we can say NWDN, yeah, these get kinda confusing, is gonna be the weekday name of WD.

0:07:05
And I don't, anything that I plan on actually using in a form or a report, okay, I give those good names. Like I'm going to use next biz day. All right, so 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 next biz day. So there, Thursday to Friday, Saturday to Monday, Sunday to Monday, Friday to Monday, to friday saturday to monday sunday to monday friday to monday and so on so it looks like it's working just fine and 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 uh... 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 I want you, 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. Go ahead. Come on, you're holding up class. Let's go. Come on. Seriously, get moving. I'm not kidding. Do this. Do the homework.

0:08:21
Now! All right, previous business day. Pre-biz day or whatever you want to call it, if today's weekday of the date, all right, if the weekday equals 2, remember Sunday is 1, Monday is 2, then we're going to set it to D minus 3, it's going to back it up to Friday, it's just D minus 1. Okay, and one more. All right. I think, yeah. Hit okay. Run it. Looks good. All right. The 6 goes back to the 4th. Okay. And now we'll put the other stuff in there that we had. Let me make these a little bit smaller. You can see everybody. All right, so this is going to be PWD, not a password, is going to be the weekday of previous day. And then PWDN, name, right, is going to be name he'd and now final shot there we go there's a very vague next business is friday previous business is a wednesday is a sunday but that if today is a there's a monday it goes back to friday looks like it's working you should never see a Saturday or Sunday in here.

0:10:03
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. Now, 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. or something that if you want to have it not calculate you know new year's day that kind of stuff bank holidays they need a more advanced function i do have another video called workdays it calculates the number of workdays between two dates very similar to this this video that we just looked at only calculates the next business in the previous business day workdays will actually count the number of days between two dates and in the extended cut for this video i do show you how to exclude holidays and company-specific days off.

0:10:54
We basically create a table that's got those dates in it, floating holidays, and we also have static holidays, things that you always know the date. For example, Christmas is always on December 25th. So this extended cut video for the members talks about how to do that. I'll put a link to this video down below. Now what I showed you here only works in a query and 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 are going to be able to just slap it right into any field that we want to. So silver members and up get access to all of my extended cut videos.

0:11:41
There's lots of them, there's 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. And 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 okay.

0:12:10
It used the date add function, which technically will work just fine. But again, like I said, if you know date math and access, you don't need a special function for that. You just have to just add the number of days. But it looks like it's got the same logic, right? If D equals six, then it adds 3. Okay, so, all right, 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 1. You can actually change this. There's a parameter to change that. You go to Microsoft's site, look up the syntax for weekday.

0:12:50
There's an optional second parameter. And you can say the first day of the week is a different day of the week. So with this, you'd have to use the optional parameter to say that the start of the week is Monday instead of Sunday. So in this particular case, exactly what it's got here, chat GPT 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.

0:13:20
Okay, we'll let it slide. The best thing about ChatGPT is, and I've been using it a lot the last couple of days, the first attempt that it gives you isn't always correct, but it will learn from its mistakes. If you say now that, okay, that's the issue, that it's not the first day of the week, it will come back usually with the correct answer so try it yourself plug it in see you know see you see what you get all right so that is your tech help for today oh hang on a second there let me slide a little bit as missing something already here that's your that's your fast that's your fast it that's your tech help video for today what's the difference everyone always asks me that I don't know tech helps are usually a little more longer and involved and involve a question from one of my students, whereas fast tips are usually just things I want to throw out there quickly that I just feel like doing.

0:14:11
But sometimes the fast tips have a question, sometimes the tech helps don't. It's whatever I feel like doing. I'm probably going to merge them together at one point. I don't know. But I hope you learned something today. Live long and prosper, my friends, and I'll see you next time. If you enjoyed this video, please give me a thumbs up and post any comments you may have. 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, and click on the bell icon to select all to receive notifications when new videos are posted.

0:14:41
Make sure you click the Show More link down below the video to find additional resources and links. 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, so if you'd like to get an email every time I post a video, click on the link to join my mailing list. Even if you don't want to become a member, feel free to donate to my tip jar. Your patronage is greatly appreciated and will help keep these free videos coming. I got puppies to feed. How do you become a member?

0:15:15
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 Tech Help 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 Tech Help 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 tech help questions to me, and you'll get one free expert class each month after you finish the beginner series. Platinum members get all the previous perks plus even higher priority for tech help questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for access to, I also teach Word, Excel, Visual Basic and lots more.

0:16:13
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'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. But don't worry, these free tech help videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It's over four hours long and I just updated it for 2021. You can find it on my website or on my YouTube channel. I'll include a link below that you can click on. And also, if you like level one, level two is just $1. Yep, that's all $1 and it's free for all members of my YouTube channel at any level, even supporters. Want to have your question answered in a video just like this one? Visit my tech help page on my website. You can send me your question there. While you're on my site feel free to stop by the Access Forum. Lots of good conversations happening there. Be sure to follow my blog, find me on Twitter, and of course YouTube. Once again my name is Richard Ross. Thank you for watching this Tech Help video brought to you by AccessLearningZone.com. I hope you enjoyed this video and that you learned something today. I'll see you again soon.

0:17:39
something today. I'll see you again soon.