Date Buttons
By Richard Rost
4 years ago
Create Buttons to Change a Date Field in Access
In this Microsoft Access tutorial, I'll show you how to create little buttons to move a date field up or down a day or week with a single click.
Carly from Chandler, Arizona (a Platinum Member) asks: I spend a lot of time changing dates for appointments. I usually have to push them back a day or two, or move them back a week or two. Typing the date in, or even using the little calendar popup thingy is a pain. Is there any way I can just make a button that says "move this appointment back 1 week?"
Members
Members will learn how to make a global function that can be called from any form in the database to change any field. We will call the function as an event property. We will use a DateAdd compatible combo box to specify the interval.
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, change date buttons, Add Current Date with a Button
Subscribe to Date Buttons
Get notifications when this page is updated
Intro In this video, I will show you how to create date buttons in Microsoft Access that let you quickly adjust appointment dates with a single click. We'll cover how to add buttons to your form to move a date forward or backward by a day or a week, as well as set it to today or tomorrow, using simple VBA code. You'll also learn how to handle null values and customize your date field to display the day of the week for easier scheduling. This video is great if you want to make changing dates fast and convenient in your Access database.Transcript I decided to leave, I made a goof, I accidentally almost made this a Fast Tips video instead of a TechHelp video. I realized it as I was doing the intro and when I went to rewatch it after I was done, it made me laugh. So I'm going to leave it in for you guys and hopefully it brings a chuckle to your day.
Here we go. 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 make date buttons, little buttons you can put next to a date field so you can click on them and go forward a day, or back a day, or forward a week, or back a week, or set it to today, or tomorrow, or whatever else you want.
Today's question comes from Carly in Chandler, Arizona, one of my Platinum members. Carly says she spends a lot of time changing dates for appointments. She usually has to push them back a day or two or move them back a week or two. Typing the date in, or even using the little calendar pop-up thingy, is a pain. She loves the calendar pop-up thingy. She asks if there is any way to just make a button that says "move this appointment back one week."
Yep, not a problem Carly, I'm going to show you how to do it, but first, this is a developer level TechHelp, which means you're going to need a little bit of VBA. We literally need like two lines of VBA at the most. So if you've never done any VBA programming before, go watch this video. It's about 20 minutes long and will teach you everything you need to know to get started with VBA. It's not hard. Don't be scared.
Also, while you're at it, I need you to know what "null" is. If you don't know what null values are, go watch this video. We're going to do a little If Then clause, so go watch that too.
Here I am in my TechHelp free template. This is a free database. You can download a copy up on my website if you'd like to. You'll find links for all this stuff down below in the description under the video.
Now what Carly is saying is she's got a date value, like this one here. If the customer wants to push it back two or three days or a week or so, you have to come in here, type the date in, or click this thing and count like one, two, three weeks back, whatever. These are handy, but it's not the best thing. So wouldn't it be nice if we could make a little button here that just goes "push it back a week"? You want to push it back three weeks? Click on it three times.
Let's make little buttons down here to go forward a day, back a day, forward a week, and back a week. We'll also make little buttons to set it to today's date or tomorrow's date. If you have an appointment that you missed and it was two weeks ago, you just click "tomorrow" and it will make it tomorrow's date.
All right, so let's add an appointment date to our table. Go to CustomerT design view. You could recycle this date if you don't care about it anymore, but there's a lot that goes into that, so let's just make a new one: ApptDate. That's our appointment date. It'll be a Date/Time value. I'm not going to put a default value in it because normally when you add a customer, patient, or whatever, you don't usually set up an appointment date right off the bat. Unless you do, that's up to you, but I'm going to assume that they don't have a default appointment date. I'm going to save that, so that means new values will be null.
Let's go into the customer form now. To save some space and time, I'm going to just delete these guys here. Let's get rid of those. Form Design, Add Existing Field. Let's grab that appointment date, slide it right there.
I'm going to format paint from, let's say, this guy to get my colors. There we go. There's my appointment date field. Make it look all pretty. Appointment: Base Date. There we go. Let's make sure it works.
Save it. Close it. Open it back up. There's my appointment date field. Put a date in there. Looks good. And yes, I'm using the ISO date standard: year-month-day. Since I've got students all around the world with all kinds of different date formats, this makes sure that everyone's on the same page. So I use this.
Another thing that's nice, especially when you're dealing with appointment dates, is I like to see the day of the week on here. I'm going to open up the properties for this guy. Normally the default date format is Short Date, which for me looks like that because I've got my Windows system dates after that. But I want to see the day of the week there. So I'm going to type in "ddd yyyy-mm-dd." That's going to give me the three-character day of the week like Mon, Tue, Wed, followed by the year, month, day. You set that in the custom format. Want to learn more about those date formats? Check out my Format Property and Function video.
If I save this and close it, let's take a peek at what I got now. There we go. That looks nice. It's easier for setting up appointments if you can see the day of the week there. Maybe let's right-align this guy too. There we go.
Now, right down here, let's put a button I can click on to set this guy to today's date. Go to Form Design. Grab a button, drop it right there. Cancel the wizard. We're going to make our own. For the caption, we're going to put "Today" in here. Let's change the font size, make it a little bit small. Let's go to 9 point. There we go, nice and tiny right there.
Let's set the button's name. Double click on it. We'll call this guy the today button BTN. Put some code in it: right click, then go to build event. That will open up the code builder. Here we are at the today button click. This will happen when we click on that button.
What do we want to do? We're going to say AppointmentDate = Date(). That's it. Just to be safe for the Date function, I like to put parentheses after it. Even though Access gets rid of them, I'm in the habit of doing that because sometimes like if you use it as a default value in a table, it'll change that to "Date" looking like this, which isn't what you want. That's the word "Date." So we want the Date function. Just out of habit I like to do that even if Access gets rid of it.
So what does that say? It says when I click on this button set the AppointmentDate field equal to today's date. That's it.
Save it. Close that. I like to close my forms between runs here. We'll click the button. Today is November 27th, so I'm going to hit it. Boom. There we go. Sunday the 27th. Yeah, I do some work on a Sunday. Took a few days off for Thanksgiving, so I get some more work done.
How about another button for tomorrow? Go to Design View. Copy this guy. Control C, Control V to copy/paste. I'm going to put "Tomorrow" as the caption. Don't forget to give it a name. Not Command33. Call it "TomorrowButton." That's fine. If you don't, Alex will yell at you.
Right click, Build Event. AppointmentDate = Date() + 1. With date values, one is a day. A week is seven days, and so on.
Let's come back out here. Save it. Close it. Open it. Click the button. Boom. There's tomorrow. Nice and easy.
If you want to do yesterday, same thing. Put a "Yesterday" button over here. Make it Date() - 1. That's how dates work. Want to learn more about this? Check out my Date Math video. Hours, minutes, and stuff work as fractions of a day. That's pretty cool.
Now, how do we handle going plus a day or minus a day? Well, instead of setting it to today's date, we just look at the date that's already in there and add one to that.
Design View. Again, we'll copy one of these guys. Copy/paste. Let's make this "+D" and we'll name this guy DateUpButton. Right click, Build Event.
This is now going to be:
AppointmentDate = AppointmentDate + 1
So whatever's in that box already, add one to it. That'll add a day to it.
Save it. Go back out here. Maybe shrink this guy up a little bit. There you go. Test it: "+D" adds a day. Put "-D" next to it for minus a day.
Save it. Close it. Open it up. And click '+' several times. See how easy it is to move forward a day now? Want to move forward three days? Click, click, click. There's three days. Nice and simple. It's a lot easier, I think, than clicking in here and using the calendar pop-up.
Now this works great unless you go to a record that doesn't have an appointment date and you hit "+D." Nothing happens, because that's null. What happens if you add anything to null? The result is null. So we've got to look and see if that value's null first, and if it is, maybe default to today's date.
Let's go back to our code window. There are two ways you can do this. I'll show you the easy way first, then a slightly more compact way.
The easy way to do this is to say:
If IsNull(AppointmentDate) Then
AppointmentDate = Date()
End If
AppointmentDate = AppointmentDate + 1
That's the easy way. Save it. Go back out here. Click the button. Boom. There's tomorrow's date.
This uses IsNull and an If Then statement, but you can get rid of all that if you know how to use Nz. Got a whole video on it. Nz stands for "Null to Zero," and the zero can be whatever you want. Usually zero, because often you're looking up IDs, but that value can be anything.
Here's how it works. Instead of that big long thing, you can say:
AppointmentDate = Nz(AppointmentDate, Date()) + 1
So it will look at AppointmentDate first. If it's null, it'll set it equal to today's date, then add one to it. That's a nice compact statement.
Save it. Give it a good compile once in a while: Debug - Compile. Just check for errors. Then come out here, close, and open it up. Let's go to someone who doesn't have an appointment date and hit the "+D" button. There you go. Nice and simple. This stuff isn't hard.
Let's make a couple more buttons: minus a day, and plus and minus a week.
Design View. Copy this guy, paste, slide it right there. Make this "-D." Change this guy's name, which is "DateUpButton," to "DateDownButton." Right click, build event, and just copy the previous code. We will change the "+1" to "-1."
Copy both of these guys, paste, drag them over here. These will be "-W" and "+W." Name them "WeekDownButton" and "WeekUpButton." Right click, build event, copy the previous code. For minus a week, you go minus seven days; for plus a week, right click, build event, go plus seven days.
Save it. Close this. Save changes. Open your customer form. Test: plus a week, yes. Seventh, fourteenth, twenty-first, twenty-eighth. Minus a week. Tomorrow, today, minus a day, plus a day. Look at that.
If you want to add other increments, like two weeks or four weeks, just add more buttons. If you're always adding increments of four weeks, make yourself a four-week button that adds 28 days.
What if you want to add months, like go from November 28th to December 28th, for regular service visits on the same day each month? Or add a year? Or use this with multiple date fields on different forms?
We will cover all of that and more in the extended cut for the members. We're going to make a function - a global function - so that we can pass any control on any form to it and say, "Take this appointment date field and set it to tomorrow, or add a day to it, or add a month, a week, or a year." We'll use a function as an event property so you don't have to have separate code for each button that we create. We'll set up an interval combo box where you can pick any interval, which will be compatible with the DateAdd function. You can add a year, a quarter, a month, an hour, or a second if you want to. All of this will be covered in the extended cut.
Silver members and up get access to all of my extended cut videos. There are over 300 by now. Come and join - it's not expensive. Gold members can download these databases that I build in these classes and have access to the code vault where all these functions are located.
That is your TechHelp video for today. I hope you learned something, and I'll see you next time.Quiz Q1. What is the main feature added to the database in this video?
A. Date auto-formatting for printed reports
B. A series of buttons to adjust date fields quickly
C. Popup alarms for missed appointments
D. Automated email reminders
Q2. What does VBA stand for in the context of this video?
A. Visual Business Administration
B. Visual Basic for Applications
C. Variable Base Assignment
D. Visual Batch Algorithm
Q3. What field type should the new ApptDate field have?
A. Text
B. Number
C. DateTime
D. Currency
Q4. Why does Richard use the ISO date standard (yyyy-mm-dd) format?
A. It looks better in Access forms
B. It is easier for American users to read
C. It ensures consistency for users worldwide
D. It is the default format in Microsoft Access
Q5. Which format string does Richard use to display the day of the week along with the date?
A. "Short Date"
B. "yyyy-dd-mm"
C. "ddd yyyy-mm-dd"
D. "dddd-mm-yy"
Q6. In the example, what does the VBA statement AppointmentDate = Date() accomplish?
A. It deletes the current appointment date
B. It sets the AppointmentDate to tomorrow's date
C. It sets the AppointmentDate to today's date
D. It displays the formatting for the appointment date
Q7. How is the "Tomorrow" button's VBA code different from the "Today" button?
A. It uses Date() - 1
B. It uses Date() + 1
C. It uses Date() + 7
D. It uses Today() + 1
Q8. How do you increase the AppointmentDate by one day using VBA?
A. AppointmentDate = Date() + 1
B. AppointmentDate = AppointmentDate - 1
C. AppointmentDate = AppointmentDate + 1
D. AppointmentDate = Tomorrow()
Q9. What problem arises if AppointmentDate is null and you try to increment it?
A. Access crashes
B. It sets AppointmentDate to zero
C. The result remains null and nothing happens
D. It generates a random date
Q10. What VBA function can compactly handle the case where AppointmentDate is null before adding a day?
A. ZeroFill()
B. NullValue()
C. Nz()
D. IfThenNull()
Q11. What does the Nz(AppointmentDate, Date()) + 1 statement do?
A. Always sets the AppointmentDate to null plus one
B. Adds one day to today's date, regardless of existing value
C. Adds one day to the current AppointmentDate, or to today if AppointmentDate is null
D. Only works with integer values
Q12. How do you set up a button to add or subtract a week from an appointment date?
A. Increment date by seven months
B. Use AppointmentDate = Nz(AppointmentDate, Date()) + 7 or -7
C. Use Date() / 7
D. Use a macro instead of VBA
Q13. What is suggested for handling increments other than a day or a week?
A. Only allow daily changes
B. Add more buttons for specific increments like two weeks or four weeks
C. Change the system date settings
D. Remove the appointment date field
Q14. If you want to handle months or years, what function does the extended cut promise to implement?
A. MonthIncrement()
B. AddYearsAndMonths()
C. A global function using DateAdd
D. DateConvert()
Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-C; 9-C; 10-C; 11-C; 12-B; 13-B; 14-C
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 all about enhancing your forms in Microsoft Access by adding date buttons that make setting and adjusting dates quick and easy. I'm Richard Rost, your instructor, and today I'm going to demonstrate how to create convenient buttons you can use alongside any date field. These buttons will allow you to quickly step dates forward or backward by a day or a week, or even set the date to today or tomorrow, all with a click.
This lesson was inspired by a question from Carly, who often needs to reschedule appointments and finds typing in dates or using the calendar pop-up to be cumbersome. She wanted to know if it's possible to create a button that simply moves an appointment back by a week. The answer is yes, and I'm going to walk you through how to set this up. Be aware, though, that this is a developer-level TechHelp video, so you will need a basic understanding of VBA. Fortunately, the code you'll need is minimal, just a couple of lines, so if you're new to programming in Access, I have recommendations for some introductory videos on VBA and handling null values to get you ready for this.
I'm working within my free TechHelp template database, which you can find on my website. The template comes with all the necessary setup for hands-on practice.
The scenario is as follows: You have a date field, such as for an appointment. Normally, to change it, you either need to retype the date or use the calendar picker. While helpful, these tools are not always the most efficient, especially for frequent changes. Adding simple buttons to increment (or decrement) the date by common amounts makes the process far more efficient.
To get started, we need an Appointment Date field in our table. You can add this in table design view, making it a Date/Time field without a default value. This means if a new record doesn't have an appointment, the value is null by default.
After updating the table, add the appointment date field to your customer form and tidy up its appearance. I personally prefer displaying dates in the ISO format (year-month-day) as it avoids confusion regardless of international settings. It is also helpful to display the day of the week alongside the date, which you can do by customizing the format property using something like "ddd yyyy-mm-dd". This immediately tells you, for example, whether a date falls on a weekend or not, which is useful for setting appointments.
Now, let's create the first button, which will set the appointment date to today. Design your form, add a button, give it the caption "Today", and adjust its appearance to suit your layout. In the button's click event, you'll add some VBA to assign the current date (using the Date function) to the appointment date field. Even though Access sometimes adjusts the syntax, I recommend always using parentheses with functions like Date. Clicking this button will update the field to today's date.
You can duplicate this process to create a "Tomorrow" button. Just copy the original button, change the caption, and update the VBA to set the date field to today's date plus one. Similarly, you can make a "Yesterday" button by setting it to Date minus one. If you're interested in how date arithmetic works in VBA, I encourage you to watch my Date Math video, as adding or subtracting numbers to a date in Access simply changes the date by that many days. Time increments work as fractions of a day.
For moving dates forward or backward by single days relative to whatever's already in the box, you can make "+D" and "-D" buttons. These will take the current value of the appointment date field and add or subtract one. This allows for quick, repeated increments or decrements. However, you need to account for what happens when the date field is empty (null). In Access, adding anything to a null value returns null. To handle this, use an If statement and the IsNull function to check for nulls and set a default if necessary. Alternatively, the Nz function is a more compact solution, letting you specify a default value if the field is null. For example, you can set the default to today's date.
Copy and adjust your buttons for weekly increments and decrements as well. For "-W" and "+W", define their actions as adding or subtracting seven days. You can also create buttons for other common intervals, such as two weeks or four weeks, as your needs require.
If you find yourself needing to move dates by months or years or want to reuse this functionality across multiple forms and different date fields, these topics will be covered in detail in the Extended Cut of this lesson. In that session, I will show you how to create a reusable function that can be used with any date control on any form, allow for more flexible intervals using a combo box, and integrate the DateAdd function for a wide variety of date increments. We'll also look at setting up global code so you don't have to duplicate the VBA for every button. Extended Cut videos are available to Silver members and above, and Gold members have access to downloadable versions of these databases and the full code library.
That concludes today's TechHelp lesson. For a step-by-step walkthrough covering everything we've discussed, visit my website at the link below to find the complete video tutorial.
Live long and prosper, my friends.Topic List Adding an appointment date field to a table
Formatting date fields to show day of the week
Custom date formats in form fields
Adding date navigation buttons to forms
Writing VBA to set a date field to today's date
Writing VBA to set a date field to tomorrow's date
Writing VBA to increment a date field by one day
Writing VBA to decrement a date field by one day
Handling null values in date fields using IsNull and If Then
Using the Nz function to handle null dates in VBA
Writing VBA to increment a date field by one week
Writing VBA to decrement a date field by one week
Copying and modifying buttons for different date increments
Testing and validating date button functionality in forms |
