Future Dates Only
By Richard Rost
4 years ago
Move Any Dates Entered MM-DD to the Future
In this Microsoft Access tutorial, I'm going to show you how to make dates entered as MM-DD always become future dates. This is great for setting appointments, follow-ups, or other calendar items. This way, if you're entering appointment dates and the month and day are before today's date in the year, they won't automatically get set to past dates. For example, today is December 14, 2022. If I am setting a future appointment and I type in "02-24" in my date field, Access will assume I mean this year, so I get 2022-02-24. In this video, I'll show you how to make it default to 2023-02-24.
Gianna from Point Pleasant, New Jersey (Platinum Member) asks: I have a database where I enter a lot of appointments. These dates will always be in the future. If it's currently December, and I type in 2/20, then the database assumes I mean 2/20 of this year. Is there any way to make it advance to next year if the date is in the past?
Members
Members will learn how to turn this into a function so that it can be used by any date field in your database very easily. We will also add some code to allow the user to specify the year, which will then not change the date. If they enter only the month and day, then the function will modify the year accordingly. Otherwise, if they do specify the year, it will keep exactly that year.
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
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, date next year, future date, force dates to next year, upcoming appointment dates, followup dates, future payments, text property, move date to future, always specify future year
Intro In this video, I will show you how to automatically adjust dates entered in your Microsoft Access database so they are always set to a future date, which is useful for managing appointments and follow-ups. We will use a little VBA programming with an After Update event, an If Then statement, and the DateSerial, Year, Month, and Day functions to ensure that any date typed in that would fall in the past is updated to the next year. This video is great for anyone needing to simplify data entry for future-focused scheduling in Microsoft Access.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 change any date entered into your Microsoft Access database into a future date. This is handy for appointments, follow-ups, upcoming payments, and lots of situations like that.
For example, if today is, let's say, December 15th, and I type in 12/20, then the database will change it to 12/20 coming up later this year in a couple of days. However, if I type in 3/5, I want the database to change it to 2023, March 5th of next year, because it's going to be a future date. There is no sense in entering in a past appointment date. That's what we're going to learn how to do in today's class.
Today's question comes from Johnna in Point Pleasant, New Jersey, one of my platinum members. She says, I have a database where I enter lots of appointments. These dates will always be in the future. If it's currently December, and I type in 2/20, February 20th, then the database assumes I mean 2/20 of this year. Is there any way to make it advance to the next year if the date is in the past?
Yes, this is going to involve a little teeny, tiny bit of programming. That's why I marked this as a developer video, but don't let that scare you. If you've never done any VBA programming before, go watch this video. It's about 20 minutes long. It teaches you everything you need to know. Don't be scared. It's only a couple of lines of code we need today, and I'll show you exactly where to put it.
We're also going to use an after update event. This is an event that runs whenever a field is changed or updated. So go watch this video if you've never done that before.
We're also going to use an if then statement. We're going to use the date serial function, which allows us to manipulate the different components of the date to build a new date. Finally, we're going to use the year, month, and day functions to get those different components, to get those pieces of the date to put together a new date value. Go watch all of these videos if you don't know what any of this stuff is. They're all free. They're on my website. They're on my YouTube channel. I'll put links down below for you to click on. Go watch them and then come on back.
So here I am in my TechHelp free template. This is a free database. You can download it on my website. Let's pretend that this is an appointment date. In fact, we're just going to change the label. We're not going to change the whole field itself. We're just going to change this label here to "Appointment Date," but it's still "Customer Since." You get what I'm doing.
I have a date here. This is the customer's appointment. It's currently December 14, 2022. So if I type in 12/30, no problem. I'm using the ISO date standard, which is year-month-day. Want to learn more about that? I'll put a link down below.
Now, if I want to type in an appointment date, let's say this appointment is in a couple of weeks after the first. So let's say it's January 15. Hit tab, Access assumes you mean January 15, the current year. It always assumes the current year. That is annoying if the field is always going to be used for things that happen in the future, like an upcoming appointment, an upcoming payment, a follow-up date, and so on. You don't want to have to go in here and remember to type in 2023-01-15 or whatever format you're using. This works regardless.
So how do we do that? Fortunately, this is one of those videos where, if you watched all the other videos I told you to watch at the top of the class, then you could figure this out on your own. This is one of those Lego videos. I'm going to show you how to put together all the pieces of the other parts in a different way. Nothing new here. This is just assembling in a different way.
Go to Design View. Go to the "Customer Since" field. We're going to use the After Update event. You could use Before Update if you wanted to, but After Update is fine. This happens after the user types in that value.
My code builder opens up here.
If the user types in a date in the future, that's fine. Leave it. If they type in 2/15/23, leave it alone. But if "Customer Since," that's my date field, is less than or equal to today's date, then what do we want to do? We're going to add a year to it.
Now, we have to be able to get the component parts of that "Customer Since" date. I can take the year and add one to it, but keep the same month and day. So what I'm going to say is:
Customer Since =
Now how do we put together our own date? That's the DateSerial function. DateSerial takes three bits of information. The first thing it wants to know is what year? Well, I want next year. So I'm going to say the year of today's date plus 1. That says give me the year of today's date. It's a date function.
Comma, what month do I want? I want the month of whatever date the user typed in, "Customer Since." Comma, same day, day of "Customer Since." Close that all up. End If.
And that's it. You're done. VBA programming is not scary, and once you get just a few commands down, you can really do some cool stuff.
So what's going to happen is the user will type a value into "Customer Since." Now, when they type that value in, Access is automatically going to add the year to it if they don't specify a year. If that date they typed in is less than or equal to today, then if you want to, you can make it just less than today if you want. That's up to you.
If you're typing in a future appointment date, and they have an annual appointment, if I type in 12/14 and it is 12/14, I want it to assume maybe next year. But that's completely up to you.
So at this point, let's say they typed in October 1, which is in the past. Access is going to add 2022 to it, so this date is in the past. We're going to build our own date using DateSerial. I want the current year plus 1, which is 2023 because today is 2022. Then the month of whatever the user typed in, and the day of whatever the user typed in. That's how you build that date.
Save it. Give it a quick compile - Debug Compile - just to make sure. Come back over here. Close the form and open it back up again.
Remember, today is December 14, so I'm going to type in 12/20. Now I'm good. I got 12/20/2022, which is correct.
Now, let's say I type in January 4. Look at that - 2023, January 4. And if you have years further in the future, if the user specifies the year, like 2025-2-4, it'll keep it because it's not in the past. It's only going to modify dates that get entered into the past. So if I come in here and type in 2003-9, it is going to change it to next year, 3/9.
That is one problem with this method: if you need to be able to go back and retroactively change dates to a past date, you'll have to either make another field, go into the table directly, or you can watch my extended cut where I cover how to do that. We'll make a function for a future date. Here it is right here if you want to type it, and if you don't know where to put it, you put this in a global module, and then you can use it for any field. I'll show you how to do that.
Then we'll make a "FutureDate" function. What that will do is allow the user to specify any year they want. It'll say, if the user doesn't specify a year, then assume either this year if it's in the future, or next year if it's in the past, just like we just did. But if they do specify a year, it will keep that year. I'll show you how to do that in the extended cut.
Plus, gold members, this is in the code vault for you, so you can go and copy and paste it. Silver members and up get access to all of my extended cut videos for both TechHelp and Fast Tips videos. There are lots of them, over 300 now. Gold members, of course, can download these databases and you get the code vault. So what are you waiting for? Join today.
I hope you learned some things, and this has been your TechHelp video for today. I'll see you next time.Quiz Q1. What is the primary goal described in this video for processing date entries in Microsoft Access?
A. To automatically change any entered date to a past date
B. To ensure all entered dates are converted to the current date
C. To transform any date entered into a future date
D. To require users to always specify the year when entering dates
Q2. Why might you want the database to assume a future year when a month and day are entered that have already passed this year?
A. To prevent historical data entries
B. Because the database is only for future appointments or payments
C. To avoid confusion between months and days
D. So that records are deleted after the date passes
Q3. Which event procedure is recommended for adding the code to change the appointment date after user input?
A. On Current event
B. Before Update event only
C. After Update event
D. On Open event
Q4. What programming language is used to write the logic that updates the date in Access?
A. SQL
B. Python
C. VBA (Visual Basic for Applications)
D. Java
Q5. Which function is used in the code to construct a new date based on year, month, and day values?
A. Now()
B. DateAdd()
C. DateSerial()
D. TimeValue()
Q6. When using the DateSerial function for this solution, what should the "year" argument be set to if the entered date is in the past?
A. The current year minus 1
B. The current year
C. The year from "Customer Since" minus 1
D. The current year plus 1
Q7. What are the "month" and "day" arguments in the DateSerial function set to in the provided solution?
A. Both are set to today's date
B. They come from the values the user entered
C. Both are always set to 1
D. Both are ignored in this case
Q8. What happens if a user specifies a year when entering the date?
A. The entered year is ignored, and the code always sets next year
B. The specified year is retained unless it is in the past
C. The year is changed to this year no matter what
D. The database rejects the entry
Q9. If you want to reuse this date-changing functionality in other forms or fields, where should you place the function in Access?
A. In a global module
B. In a macro
C. In the table properties
D. Only inside the current form's event
Q10. What is a potential drawback of automatically updating dates in the way described in the video?
A. It is impossible to enter dates in the distant future
B. You cannot retroactively change past dates easily
C. It corrupts the database
D. The solution only works for subforms
Answers: 1-C; 2-B; 3-C; 4-C; 5-C; 6-D; 7-B; 8-B; 9-A; 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 covers how to automatically convert any date entered into your Microsoft Access database into a future date. This feature proves useful in databases that handle appointments, scheduled payments, follow-up reminders, or any scenario where you do not want to accidentally enter a past date.
To give you an example, imagine it is December 15 and you enter 12/20 as the date. The database should recognize that this means December 20 of the current year, just a few days away. However, if you enter 3/5 (March 5), and that date has already passed for the current year, Access should assume you meant March 5 of the next year, not last March. This approach helps prevent mistakes with scheduling past appointments, which is exactly what many users need.
A member wrote in asking how to make sure Access would treat dates entered without a year as an upcoming future date, not a past one, especially in forms where only future appointments are recorded. By default, when you type a date like 2/20 (February 20), Access uses the current year, which is not always the desired behavior.
To solve this, you only need a small amount of VBA programming. If you have never worked with VBA before, I have a detailed beginner's guide available on my website and YouTube channel that explains the basics and helps you get started. The code needed for this solution is really simple, and I will clearly show you where to insert it.
For this task, we will use the After Update event, which triggers when a field is edited and then exited. You will also need a basic understanding of the If Then statement, the DateSerial function (which lets you construct a date from its year, month, and day parts), and the Year, Month, and Day functions (to extract those parts from a date). If you are unfamiliar with any of these, free instructional videos are available on my site and YouTube channel.
In the demonstration, I use my free TechHelp sample database template, which you can download from my website. Let us imagine you are working with a field called "Appointment Date." The underlying table field is still named "Customer Since," but for our purposes, it checks future appointments. Suppose today is December 14, 2022. If you enter 12/30, Access will treat it as December 30, 2022. However, if you enter 1/15 for an appointment early next year, Access will still default to 2022, when what you really want is 2023.
What do we do differently? You want to add a bit of VBA to the After Update event of your date field. You could also use Before Update, but After Update is sufficient for this purpose. When the user types in a date, the code checks if the date falls before or on today's date. If so, the code reconstructs the date value using the DateSerial function, building a new date based on the current year plus one, but keeping the same month and day the user entered. If the date entered is already in the future, Access leaves it as is.
You simply need to extract the year, month, and day from the field the user updated and reassemble the date as needed. Once the code is in place, any time a user enters a past date (as determined by today's date), Access will bump it up to next year. The process is seamless: just enter a month and day, and Access manages the year so you always get a future appointment.
Of course, if you want more flexibility, such as allowing past dates in special situations, you can adjust the conditional check. And if a user specifies a date with a full year (for example, 2/4/2025), Access will leave it unchanged, since this date is already in the future.
One thing to consider: if you ever need to enter an appointment that's intentionally in the past, you may want to add another field or update the record directly in the table to avoid your code interfering. In the extended cut of this lesson, I walk you through how to build a reusable function that handles these cases more flexibly. I will show you how to create a global function in a module, so you can apply this logic to any date field in your database. With this approach, you can specify whether to keep the year the user typed or update only if the user leaves the year blank.
Gold members have access to the code vault and can download these database files directly. Silver members and above can watch all my extended cut videos, which now number over 300 comprehensive tutorials. Gold members get both databases and the code vault as part of their benefits.
To sum up, this technique helps keep your appointment or scheduling database accurate and forward-looking with minimal effort. You can find a complete video tutorial with step-by-step instructions for everything I have discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Changing past dates to future dates in Access
Using the After Update event in forms
Accessing and editing form field events in Design View
Writing VBA code for date adjustment
Using If Then statements for date logic
Applying the DateSerial function in VBA
Extracting year, month, and day components from a date
Automatically updating dates based on current date
Handling user date input without specified year
Ensuring only future dates are accepted in a form
Testing and compiling VBA code in Access |
