|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
First Monday By Richard Rost 
5 years ago
How to Find the First Monday of any Month In this video, I'll show you how to find the first Monday of any given month. You can then, of course, very easily use the same technique to find the first of any given weekday in that month (Monday - Sunday). Alissa from Ottawa, Ontario, Canada (a Silver Member) asks: I need to be able to find the first Monday of any given month to determine what the first work day is. How can I do this in Access? MembersSilver Members and up get access to an Extended Cut of this video. Members will learn how to calculate the Nth weekday of any given month, such as the second Monday or the fourth Saturday. Teeka from Shelby, North Carolina (a Platinum Member) asks: Is there any way you can find the Nth weekday of any given month, for example the 1st Monday in June 2021, or the 4th Wednesday in August 1999?
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
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, first monday of the month, nth day of any month, dateserial, weekday, vbMonday
IntroIn this video, I will show you how to find the first Monday of any month based on a given date in Microsoft Access. We'll use a bit of VBA to build a loop that checks each date in the month until it lands on a Monday, and I'll walk you through setting up the form with a date picker and the code needed to make this work. You will also see how to generalize this to find the first occurrence of any weekday in a month.TranscriptWelcome 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 first Monday of the month in Microsoft Access. You'll give it a date, and it will tell you what the first Monday of the month that date falls in happens to be. Of course, you can use the same technique to find the first any day of the month, like the first Wednesday, the first Sunday, and so on. Today's question comes from Alissa in Ottawa, Ontario, Canada, one of my Silver members. Alissa says, I need to be able to find the first Monday of any given month to determine what the first workday is. How can I do this in Access? A little bit later on, for the members in the extended cut, I'm going to expand upon that. Tika from Shelby, North Carolina, a Platinum member, asks, Is there any way you can find the end weekday of any given month, for example, the first Monday or the fourth Wednesday? We are going to take the first example of just finding the first Monday and then expand it to any day of the week and any iteration of that, the third, the fourth, the fifth even. Now, before we get started, there are a couple of prerequisites. This unfortunately requires a little bit of intro to VBA. If you haven't watched my intro to VBA class, go watch it. It's only a couple of lines of code. Don't be scared of VBA. Once you learn a little bit of programming, your database is getting really, really powerful, and some things you can only do with VBA. I did look around the web. I did a bunch of Googling and tried to find just a purely mathematical way to calculate the first Monday of the month. I found a few functions, but some of them don't work 100 percent of the time. They fail in leap years, for example. I found one that I really liked and unfortunately, I put a leap year date in it and it didn't work. So I'm going to stick to what I know - using VBA and a loop. You should also know DateSerial, which allows you to build a date based on its components. You give it the year, month, day. The Weekday function returns a number one through seven, for what day of the week it happens to be. That's how we can find if something is a Monday, for example. If you haven't watched those videos, go watch those first. If not, I'll explain DateSerial and Weekday as we get to them. Before we get started, a little bit of advertising: I cover all of the date and time functions in detail in my Access Expert 27 and 28 classes. If you really want to learn how to work with dates and times in Access, those are the two classes for you. There's a link right there. I'll put it down in the link section too. Here I am in my TechHelp free template database. This is a free download off my website. Go grab a copy if you want to, and watch the video where I build this so you understand how this Hello World button works. A little bit of VBA - you click on the button and it gives you Hello World. This is just a status box. I think this is better than popping up message boxes all the time, so I built this into the template. Let's start out by putting a text box on this form so I can enter a date. We'll put a date here like that in the label. Slide that over. Let's make that white so we can actually see it. Make that a little bit bigger. Let's put today's date in here. We're going to go over here to the properties. The name is going to be myDate. Don't use the word Date. Remember, Date is a reserved word, so function. Let's format that as a short date. That'll give us the date picker button that pops up too. Let's go to Data and put equals today's date in there just like that. Now, let's see what we have. Close that, open it back up again, and there we go. Just today's date, you can click on it. Sometimes that little pop-up helper button hides off the edge of the form, so we're going to bring this away from the margin just a little bit like that. Save it and take one more peek at it. There we go. Now I can just pick a date with the date picker. Let's make these a little bit bigger right up here. Find first Monday. Then we'll make the status box a little bit bigger so I can actually see what's in it. Save that. Let's go into the code editor, right-click on the button, and go to Build Event. That should bring this up. I don't want status Hello World. If you don't know what status is, it's my own little function for putting stuff in the status box. I show you how to build that in the template video. Here's what we're going to do. We're going to say, I have a date. I want to find the first day of that month and then check to see if it's Monday. If not, add a date to it. Check to see if it's Monday. If not, add a date to it. Eventually, I'll get to a Monday. That's how we're going to find the first Monday. So, Dim D as Date. That's a variable. D is a variable. You can hold a date value. Let's start at the first day of the month. How do I get the first day of the month? We're going to use the DateSerial function. DateSerial says, give me the pieces of the date that you want: the year, the month, and the day, and I'll return an actual date value. We're going to say D = DateSerial. Now, if you want to just type in today's values, I could do 2021, 7, 26. That would be valid, and that would return an actual date of July 26, 2021. But I don't want to hard code that in there. I want to use the values from this field right here, the myDate field. So I'm going to break this up into its pieces like this: year of myDate, that'll return 2021, month of myDate, that'll return a 7. Now I want the first day of the month, so put a 1 there. No matter what date I have in that box, I'm going to get the first day of that month, and we're going to put that in D. Now I want to check to see if D is a Monday. I'm going to use the Weekday function. If not, I'm going to loop and add one to D. I'm going to take advantage of the fact that, in Access, a value of a day is equal to one. If I take January 1st and add one to it, I get January 2nd. So I'll use a little while loop: while Weekday of D. That'll return a number from one to seven - one is Sunday. If that's not equal to vbMonday... What's vbMonday? vbMonday is a constant that Access knows. Every day of the week has a constant: vbSunday, vbMonday, vbTuesday, vbWednesday, and so on. I make a little song for it. If you want to put comments in your code here, you should probably do that. First day of selected month. Here we're going to say: loop while D is not Monday. Now, if this is true - if Weekday(D) is not Monday - I'm going to add one to the date. D = D + 1. While... End. That's it. So this loop is going to run until it finds the first Monday. That's it. That's all you need. Like I said, three lines of code. Actually, technically, we could get away with probably three lines of code here, but I like to phrase it like that. Then we're going to say status: the first Monday is (and then D). That's it. Let's get rid of some of these empty blank lines here. Save it, come back out here, close that down, open it back up and click. First Monday is 7/5. Let's verify it by looking at the little date picker here. Yep, 7/5 is the first Monday. Let's change the date. Let's go to August. Pick August 19th, for example. Click. Boom. 8/2 is the first Monday. Say 8/2. Perfect. Let's try a leap year. Let's go back to... Actually, I like to always try 2/1/2000, even though 2000 is not a leap year, I always like to try that. 2/7. Let's see. 2/7 is the first Monday. Let's try 1994, for example. Go. 2/7. Okay, good. Let's try 2/29/1990, and go. Well, not valid. So 2/29/1990 is not a valid date. Let's see. 2/28. Yeah. Beautiful. How about 92? Okay, good. All right. Looks like it's working. It's checking out. That is how, with a little loop right there, you can figure out the first Monday of any month. Again, I have seen some calculations online that are just math equations, and they will be more efficient if you use them in a query. If you can do this purely mathematically, then that will be faster, and if you have a big query and you want to calculate lots of them. But I tried a couple of them, and I put some dates in, and they just did not work right. They didn't take all dates into consideration. If you're going to go through that, you might as well just do a little loop. I tend to find that looping with VBA solutions - I mean, this works 100 percent of the time. There is no date that's going to not work with this. You start at the first, you count up until you find a Monday. That's it. For the members extended cut, we're going to calculate the end weekday. So if you want the fifth Sunday or the third Thursday, a very similar technique, we're going to loop until we find it, and then we're going to create a function out of it. We will make a global function you can use anywhere in your database, not just necessarily on this form. That's all covered in the extended cut for members. Silver members and up get access to all of my extended cut videos, and Gold members can download these databases that I build. Want to become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use. Platinum members get all of the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link 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 would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the main goal of the video tutorial?A. To show how to sort dates in Access from earliest to latest B. To find the first Monday of the month in Microsoft Access using VBA C. To create a new Access database for holidays D. To calculate annual sales in Access Q2. Which Microsoft Access function is used to build a date from its parts (year, month, day)? A. DateBuild B. DateMaker C. DateSerial D. MakeDate Q3. What does the Weekday function in Access return? A. The name of the month B. The number of weeks in a month C. A number from one to seven representing the day of the week D. The number of days remaining in the month Q4. Why does the instructor recommend using VBA with a loop, rather than relying on a mathematical formula to find the first Monday? A. The mathematical method does not work in leap years and special cases B. VBA is faster for all operations in Access C. Mathematical formulas are not supported in Access queries D. VBA loops are the only way to interact with forms in Access Q5. When setting up the form, why should you avoid naming your date field "Date"? A. Because "Date" is a reserved word in Access and could cause conflicts B. Because Access does not support field names starting with "D" C. Because it makes the form look unprofessional D. Because it causes the Weekday function to malfunction Q6. What is the purpose of the VBA constant vbMonday? A. It specifies the beginning of the week as Monday B. It provides a shortcut for formatting dates C. It represents the value returned by Weekday for Monday D. It stops the loop from running on Mondays Q7. What happens in the loop to find the first Monday? A. The loop subtracts one day until it finds a Monday B. The loop adds one day to the date variable until Weekday returns Monday C. The loop checks for holidays before picking a date D. The loop displays all Mondays in the current month Q8. Which Access form control is used to let the user pick a date in the video? A. Option group B. Text box with a date picker C. Combo box with days of the week D. Checkbox for each weekday Q9. What does the instructor suggest for those not familiar with VBA? A. Skip the project entirely B. Watch the intro to VBA class first C. Learn SQL before touching VBA D. Use only macros in Access Q10. How does the solution ensure it works for all months and dates? A. By using a pre-defined list of Mondays in each month B. By hard-coding dates for each situation C. By starting at the first of the month and looping until it finds Monday D. By using only mathematical calculations Q11. What is one advantage the instructor mentions about the VBA loop solution for finding weekdays? A. It is guaranteed to work for all date scenarios, including leap years B. It is the fastest method for large datasets C. It automatically creates a calendar for the whole year D. It is more complex but teaches advanced Access skills Answers: 1-B; 2-C; 3-C; 4-A; 5-A; 6-C; 7-B; 8-B; 9-B; 10-C; 11-A 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 covers how to find the first Monday of the month in Microsoft Access, using a small amount of VBA programming. This technique not only applies to Mondays, but you can also adapt it to identify the first occurrence of any other weekday, like the first Wednesday or Sunday of any given month.The motivation behind this lesson comes from a question that asks how to determine the first Monday of any month to use as the first workday. Later in the extended cut for members, I also explain how to find other specific occurrences, such as identifying the end weekday of the month or the nth weekday (like the fourth Wednesday). To follow along with this lesson, you will need a basic understanding of VBA in Access. I recommend watching my introductory VBA lessons if you have not done so already. The code for this task is short and simple, so even beginners can manage it with a little practice. Learning some VBA significantly increases the flexibility of your databases since certain tasks simply are not possible with only macros or queries. While researching, I tried to find a purely mathematical approach to determine the first Monday of the month, hoping for a concise formula. However, the solutions I found were not always reliable, especially with odd dates like those falling in leap years. For that reason, I suggest using a VBA loop to ensure accuracy for all cases. Some knowledge of Access date functions will help here. The DateSerial function lets you create a date if you have the year, month, and day values. The Weekday function returns a number indicating which day of the week a given date falls on. For example, you can check if a date is a Monday by checking if the Weekday value matches the constant for Monday. If you are not familiar with DateSerial or Weekday, I will explain how to use both as we go through the example. I also want to mention that I cover date and time functions thoroughly in my Access Expert 27 and 28 classes. If you want to learn more about handling dates in Access, those two courses are ideal for you. To walk you through the process, I use my free TechHelp template database, available for download on my website. I have included an example form, which features a text box for entering the date and a status box to display results. The initial steps involve adding a text box to the form where you can input or select a date. It is important to avoid using 'Date' as the name for your text box, because 'Date' is a reserved word in Access. Instead, use a different name, like 'myDate', and format it as a short date to ensure the date picker appears. Once the text box is set up, I walk through the task of finding the first Monday. The process is straightforward: start by calculating the first day of the chosen month using the DateSerial function and the components (year, month, day) from the entered date. Then use a loop to check whether this date is a Monday using the Weekday function. If it is not a Monday, increment the date by one day and repeat the process until a Monday is found. The code for this is simple. Define a variable to hold your working date. Use DateSerial to create the first date of the month. Loop through each day, checking if each date is a Monday. As soon as you find the right day, display the result in the status box on the form. I demonstrate how this works with several examples, including dates from leap years and various months, to verify that the method reliably finds the first Monday in every case. The VBA loop takes care of exceptions that mathematical shortcuts may miss, making it a robust solution. For members, the extended cut of this video covers how to find other specific weekdays, such as the fifth Sunday or third Thursday of a month. I also show how to create a general function that you can use throughout your database, not just on a single form. Membership options offer additional benefits. Silver members get access to all extended cut videos and live sessions. Gold members can download all the sample databases and access my Code Vault full of helpful VBA code. Platinum membership includes everything already mentioned, plus access to my comprehensive beginner and expert courses on Access, Word, Excel, Visual Basic, ASP, and more. Of course, my free TechHelp videos will continue as long as you keep watching them. If you enjoyed this lesson, please give it a thumbs up or leave a comment. I read every comment and appreciate your feedback. To avoid missing future videos, subscribe to my YouTube channel and activate notifications. You can also join my email list for updates. If you are looking for a solid foundation in Access, try my free Access Level 1 course, which covers the essentials of database building and is over three hours long. Level 2 is just one dollar and is free for channel members. If you have a question for a future video, visit my TechHelp page to submit your inquiry. 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 ListCreating a text box to enter a date in AccessSetting properties and formatting for the date input Using the DateSerial function to get the first day of a month Extracting year, month, and day from a date field Using the Weekday function to determine the day of the week Using VBA to loop through dates to find the first Monday Using VBA constants like vbMonday with Weekday Updating a status box to display results in a form Testing with different dates including leap years Comparing VBA looping versus mathematical solutions for weekday searches |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access first monday of the month, nth day of any month, dateserial, weekday, vbMonday PermaLink First Monday in Microsoft Access |
