Optional Date Parameter
By Richard Rost
2 years ago
Optional Date Parameter in Access VBA, Default Today
In this Microsoft Access tutorial, I will show you how to create optional date parameters in VBA that default to today's date when not specified by the user. We'll tackle common errors like "constant expression required" and learn a clever method to assign default values, ensuring your functions run smoothly without requiring input dates.
Carson from Mount Pleasant, South Carolina (a Platinum Member) asks: Hi, I'm trying to write a function in VBA that requires a date parameter, but I want it to default to today's date if I don't specify one. I tried using the Date() function as the default value, but it doesn't seem to work. I get a "compile error: constant expression required." Is there a way to make this happen? Can you show me how to do it?
Members
There is no extended cut, but here is the database download:
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!
Prerequisites
Recommended Courses
Keywords
TechHelp Access, Optional date parameters, default today's date, VBA function, Access VBA compile error, public subroutine, dayAfter function, optional argument, debugging VBA, VBA date handling, ISO date standard VBA, setting default date VBA, constant expression required, compile error
Intro In this video, we'll talk about how to set up an optional date parameter in Microsoft Access VBA that defaults to today's date if the user does not specify one. We'll look at why you can't use the Date function as a default value, how to use a placeholder date like December 30, 1899, and how to check and set the parameter value in your code. You'll also see how to create a public subroutine, use message boxes, handle optional arguments, and debug VBA code.Transcript Welcome to another TechHelp video brought to you by Access LearningZone.com. I am your instructor Richard Rost. Today we're going to talk about optional date parameters, how to have an optional date parameter that will default to today's date if the user doesn't specify a date. There's a trick to it.
Today's question comes from Carson in Mount Pleasant, South Carolina, one of my platinum members. Carson says, "I'm trying to write a function in VBA that requires a date parameter, but I want it to default to today's date if I don't specify one. I tried using the date function as the default value, but it doesn't seem to work. I get a compile error, constant expression required. Is there a way to make this happen? Can you show me how to do it?"
Well, of course, Carson, I'd be more than happy to do so. Before we get started, though, this is a developer-level video. If you've never done any programming with Access VBA, go watch this video first. It will teach you everything you need to know in about 20 minutes. Also, go watch this video on creating your own function. Functions and subroutines work pretty much the same way. The only difference is a function returns a value, whereas a subroutine just does some stuff. They're both procedures, and for the purposes of this video, either one is fine. Finally, go watch this video on optional parameters so you understand how you can create a parameter that's optional. If the user doesn't specify something, then you give it a default value. These are all free videos. They're on my website and my YouTube channel. Go watch those and come on back.
All right. So here I am in my TechHelp free template. This is a free database. You can grab a copy from my website if you want to. Let's say we're going to make our own function. I'm going to go down to my global module, and I'll open up the VBA editor. I'm going to ignore all this stuff for now. Let's just come down here at the bottom. I'm going to make our own public subroutine. It's a subroutine that everybody in the database can use. I'm going to call it dayAfter. It's going to take one parameter, D as a date. You're going to send in a date, and it's going to message box that the day after D is D plus one. That's all it's going to do.
I can come back out here, and we're going to go into this button, right-click, build event. I'm going to say status and just put a message in here: doing stuff. We're going to say in here dayAfter and then today's date, let's say like that. DayAfter date. Save it. Debug compile once in a while. Close this. Let's close this. Let's come back out here and run it. Click. So, the day after 2024-11-25 is 11-26. Very simple stuff so far.
Now if I send it an actual date, let's come in here and go. Instead of this, we'll say 2025-11. Notice one of my pet peeves is that even though I use the ISO date standard and that's what's set as my regional settings, VBA still flips it back to this. I think that's already on the list of suggested stuff to send to the Access team to make sure they fix that. Anyway, I put an actual date value in there and run it. Date changes. Yes. Back out here. Hit the button. It still works. That's great.
But now here's the thing. This is what Carson's trying to do. He wants to not have to send that date value into his function because sometimes he doesn't specify it. If you don't send it in, then use today's date. Now if I get rid of the date here, go to Debug Compile, I get an error. It says argument not optional. Okay. We have to make that argument optional, which we should know how to do from our other optional parameters video.
Right-click and then definition. Now in here, we're going to make this optional: Optional D as a date, and we have to specify some default value. You might think you can put the date function in there so it gets today's date, but it says constant expression required. What is a constant expression? A constant is something that has a set value that doesn't change. You can't put a function in there. It's got to be a value.
What value could you put in there to represent today's date? You really can't without sending it in. But here's the trick. What we're going to do is pick a date that couldn't possibly happen for whatever type of database you're working with. If it's an order entry database or something like that, put in something like January 1st, 1900, or you could even use date zero. Date zero, I believe, if memory serves, is December 30th, 1899. We can use that as our seed. Most databases you build, if you're running a business, doing order entry or contact management, you're not going to be dealing with dates from the 1890s. If you've got a genealogy database, you might have a different situation, and you'll have to come up with something else. But this will work for most practical purposes.
Now if I debug compile, I'll get around that problem. Let's try it. Hit the button. There we go. You can see now it says the day after midnight, that's the zero date, is 1899-12-31. That's the day after. So it's December 30th, 1899. Anything in that zero date range, if it's less than one, it assumes it's just a time. You can store time values in there as well.
Now, that's not what we want though. We want it to default to today's date. So here's what we're going to do. We're going to just come in here and say: If D equals zero, then D equals today's date. That's it. We're just going to short-circuit it right inside the subroutine. Now save it. Debug and compile once in a while. Come over here. Hit the button. There you go. The day after today is tomorrow. That is the trick right there. You have to send in an optional date, and if they don't give you the date value, use today's date or any date you want. I put some default start date in here, January 1st, 1992, whatever. Your Legos, you put them together however you want.
Did you like that? Did you enjoy learning with me? I've got tons and tons of developer lessons on my website. Come check them out. There's a link. I think I'm up to developer lesson 45 now. There are so many of them. I cover all the different things you need to be an awesome developer in Microsoft Access. They just keep going.
That's going to do it, folks. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Optional date parameter in VBA
Defaulting to today's date in VBA
Creating a public subroutine in VBA
Using message boxes in VBA
Handling optional arguments in VBA
Debugging and compiling VBA code
Using default values for parameters
Working with VBA date functions
Setting a default date value in VBA
Using date zero as a sentinel value
Checking and setting parameter values in VBA
COMMERCIAL:
In today's video, we're learning how to set up optional date parameters in VBA that default to today's date if not specified. We'll address the compile error issue and explain how to handle it by using a placeholder date like December 30th, 1899. You'll learn to adjust your function so that if a date is not provided, it defaults to the current date effortlessly. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the purpose of the video tutorial presented by Richard Rost?
A. To demonstrate how to create a dynamic text box in Access
B. To explain how to use optional date parameters in VBA
C. To show how to design a user-friendly form
D. To discuss database backup strategies
Q2. What problem is Carson facing with his VBA function?
A. He cannot set a default value for a string parameter
B. The date function causes a compile error when used as a default value
C. The VBA editor does not allow optional parameters
D. His function does not return the correct time value
Q3. Which error is encountered when trying to set the date function as the default value for a parameter?
A. Syntax error
B. Ambiguous name detected
C. Argument not optional
D. Constant expression required
Q4. What workaround does Richard suggest for the default date issue in VBA?
A. Use the current date as a string
B. Set the default date parameter to a future date
C. Use an impossible date like January 1st, 1900
D. Store the date in a text file
Q5. Why does Richard choose a date like December 30th, 1899, as a default value for the date parameter?
A. It is the starting point for Access date calculations
B. It is a commonly recognized invalid date
C. It avoids conflicts with future date entries
D. It represents the zero date used internally by Access
Q6. How does Richard's subroutine handle an optional date that has not been specified?
A. It sets the date to a random value
B. It defaults to today's date using a check inside the subroutine
C. It throws an error and halts execution
D. It leaves the date unchanged and logs a warning
Q7. What action does Richard suggest performing frequently while working with the VBA code?
A. Restarting the VBA editor
B. Saving and debugging the code
C. Commenting out large sections of code
D. Exporting the code to a separate file
Q8. What video topics does Richard recommend watching before attempting this tutorial?
A. Creating reports, Access macros, and SQL queries
B. Beginner Access VBA, creating functions, and optional parameters
C. Forms design, database normalization, and recordset management
D. Importing data, query optimization, and database security
Answers: 1-B; 2-B; 3-D; 4-C; 5-D; 6-B; 7-B; 8-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 explores how to implement optional date parameters in VBA, particularly how to have a date parameter default to today's date if the user does not specify one. This query originates from a problem faced when attempting to set the default value of a date parameter to today's date using the Date function, resulting instead in a compile error demanding a constant expression.
Before diving into solutions, it's important to note that this tutorial is aimed at developers. It's beneficial to have a basic understanding of programming with Access VBA, functions, and optional parameters. If you need more grounding in Access VBA, creating functions, or understanding optional parameters, there are other tutorial videos available on my website and YouTube channel that you might want to watch first.
In this example, we start by creating a subroutine within a free Access database template, accessible through my website. The subroutine is named 'dayAfter' and takes a date parameter, then uses a message box to display the day following the given date. Initially, specifying a date is mandatory; omitting it causes an error because the argument is not optional.
To correct this and allow the subroutine to default to today's date when no date is provided, we make the parameter optional. While it might seem logical to use the Date function as a default value, VBA requires a constant expression, which cannot be derived from a function. Instead, we select a date unlikely to occur within the context of the database - such as January 1, 1900, or date zero (December 30, 1899) - as a placeholder.
Once this placeholder date is set, if the subroutine is executed without providing a date, and the parameter matches this impossible date, we redefine it to be equivalent to today's date within the subroutine. This approach ensures that, by default, the system will use today's date if none is specified, exemplifying a practical solution to Carson's query.
If you're eager to develop your Access VBA skills further, you can explore a wide range of lessons available on my website, which comprehensively cover various aspects necessary for proficient development in Microsoft Access.
For a complete video tutorial with detailed instructions, visit my website at the link below. Live long and prosper, my friends.Topic List Optional date parameter in VBA
Defaulting to today's date in VBA
Creating a public subroutine in VBA
Using message boxes in VBA
Handling optional arguments in VBA
Debugging and compiling VBA code
Using default values for parameters
Working with VBA date functions
Setting a default date value in VBA
Using date zero as a sentinel value
Checking and setting parameter values in VBA |
