Last Update: 2018 - 02 - 11
The Date Data Type in VBA and Access
by Philipp Stiefel, originally published: 2017-11-29
In general, we are very familiar with dates. We deal with them all the time in real life as well as in our digital lives. So, what could go wrong when we work with dates in Access databases and VBA programming?
A lot, actually!
While dates in Access and VBA seem to be simple and straightforward at first sight, there are actually quite a few pitfalls connected to handling dates. I see beginners in Access development struggle with dates nearly every day in Access related forums. - I’ll try to provide comprehensive information and to give you some guidance on how to avoid common problems when working with dates in Access and VBA.
Introduction and Basics
There is a dedicated Date/Time data type in Access and a corresponding Date data type in VBA. The first important thing to internalize is, there is always date and time stored in this data type.
A very common misconception is the idea that you can add a field to an Access table, choose the Date/Time data type, and then set the field’s Format-Property to any format that displays a date without time (e.g. “Short Date”), to achieve a date-only column in your table.
This does not work!
A Date/Time-column always stores date and time. Setting the Format-Property of a table column will only affect how the data is displayed to you, it does not affect how data is actually stored in the database. In other words, it will show you the date only but it will still store the time as well. - You need to be aware of this or you’ll be in for an unpleasant surprise sooner or later.
While we are at it, let’s take a closer look at how the VBA and Access Date/Time data type is set up internally. A thing worth knowing is that the Date(/Time) data type is just a very thin wrapper around the data type Double.
Double is an 8-byte floating point data type and can store at least 15 significant digits. The details of the Double-precision floating-point data are beyond the scope of this article. However, there is one thing about floating point numbers you should be aware of. They are not exact! If you assign a value to a floating-point variable or database column the value that gets stored might be extremely close to the number you entered but not exactly that number.
DateTime data is stored in the Double type in a simple but very effective way. The integer part represents the days since December 30th, 1899. The decimal digits represent the fraction of a day thus the time of day. Negative numbers represent dates before 12/30/1899.
A double value of 1.5 represents the Date/Time 12/31/1899 12:00 PM. And the current time (06/18/2017 15:32:01) is the Double value 42904.64723.
You can use the CDbl type conversion function to explicitly convert a Date to a Double type. Vice versa, you can use the CDate function to convert a Double to a Date. This conversion is lossless. You will not use any information when you convert between these two types.
Public Sub TestDateConversion() Dim myDate As Date Dim myDouble As Double myDouble = CDbl(Now()) myDate = CDate(myDouble) MsgBox "The Double value " & myDouble & " represents the Date value " & myDate & "!" End Sub
The above sample uses the Now-Function to retrieve the current date and time. It is immediately converted to a double and afterward back to date. The MessageBox will display different text representations of the variables depending on their type. The internal value, however, is exactly the same.
The specific Date data type does not add much to its Double core. Its main benefit is its expressiveness. By being a Date instead of just a Double the numeric value gets its true meaning. Additionally, a Date when being displayed as or converted to a string will use the General Date format for the text representation of the Date.
The CDbl and CDate-Functions in that sample are actually not required. An implicit conversion between these types works all the same. Nevertheless, I prefer to be explicit in my code. I rather type in the conversion functions to make the intention of the code clearer.
VBA Date and Time Functions
There are quite a few built-in Date/Time-related functions in the VBA.DateTime-Module. If you were not aware of that module, I strongly urge you to open the VBA Object Browser (Hit [F2] in the VBA-Editor) and take a look at the available functions.
A word on Date arithmetic. The function DateAdd can be used to add an interval (e.g. day, month, year) to a date. This can be used to add any number of days to any date. But because of the internal representation in the Double type, you could also write AnyDateVariable +1 to add one day to the date.
This simple addition is certainly less expensive (=performs better) than a function call. Nevertheless, I strongly prefer to use the DateAdd-Function for expressing the intent of the operation more clearly. If you want to add months or years, you are going to need that function anyway, as it is not as straightforward to know which numeric value you would need to add to get the desired result.
If you want to subtract an interval from any date, you simply use a negative number in the DateAdd-Function. To subtract days from a date the simple arithmetic operation described above works as well.
The DateDiff-Function is very useful to get the interval difference between two dates. There are several other useful built-in functions in the VBA.DateTime-Module, so you should definitely take a look.
After this rather lengthy introduction, let’s take a look at some common problems with dates in VBA and Access.
Date Criteria in Queries - Remember: It’s Date and Time, always!
Usually, you want to compare date values without time in queries. By trying this you will encounter a frequent problem with the DateTime data type in queries. The presence of time.
Imagine you created a table with a DateTime-Field in it. Now you want to filter the data in the table by that date value to one particular day. Seems to be a simple task.
You create a new query and enter the desired date as criteria. - But what happened now? There is a record missing from the expected result!
When you pay close attention to the data in the table, the cause becomes obvious pretty quickly. The record, which displays time next to the date, is missing.
The data displayed for the other records in the table as well in the query criteria suggest there is just the date without time. That is not true, it is just that the General Date format used to display the date in the table will not show time values for 00:00:00. Likewise, even if you enter this time data into the criterion, it is stripped out automatically. But still, it will be used to compare the data in the table with the criterion.
Formatting the data in the table with the Short Date format will not change this behavior. It only makes things worse as you will not be able to see the time data in the table anymore.
The solution for any day based date queries is to use a criteria expression to filter all Date/Time values greater or equal the start date and less than a day after your desired end date.
The delimiter for Date/Time literals in Access/Jet-SQL is the hash sign (#). So, the resulting criteria expression in the design view for my sample query is this:
>=#23.08.2017# AND <#24.08.2017#
I used the German date format here, you should type the date according to your regional settings. E.g. mm/dd/yyyy if you are in the US.
As you can see in the screenshot, all the expected records are returned by the query now.
Do not use the BETWEEN operator instead of >= and <. That is equal to >= and <= and will include 00:00 o’clock the next day, thus producing different, probably incorrect, results.
My above example was about querying the data from a single day. But all the above applies if you want to query data from a whole week, a month or even a year.
Enforcing Date-Only Values in a Table Column
As said before, defining a date-only format for a date column in a table does not at all ensure that no time data is stored in that column.
The only option to enforce the entry of dates only - that is dates with a time of 00:00:00 - is a Validation Rule applied to the table column.
For columns not allowing NULL values (Required=Yes) there are several Validation Rule expressions you can use for this purpose. It is more difficult for columns that allow Null values. The Null value will result in an error in most validation rule expressions and thus preventing you from saving the record.
Here is a Validation Rule expression you can use to enforce date only values in your nullable DateTime column:
0 = Hour([DateValueCol]) + Minute([DateValueCol]) + Second([DateValueCol]) Or IsNull([DateValueCol])
Here it is entered the Validation Rule property of a date Date/Time column in the Access Table Designer.
Building SQL Queries with Date/Time Criteria in VBA
Date values need to be properly formatted for embedding in the SQL-String of a query in VBA.
If you embed a date value into a SQL string in VBA, the date needs to be converted to its text representation. By default, Access/VBA will use the General Date format to implicitly convert a date to a string. This format is based on the date format you configured for your computer in the Regional Settings of the Windows Control Panel.
If you configured your Regional Settings for the United States, your date format will be MM/DD/YYYY. If you configured your Regional Settings for Germany, your date format will be DD.MM.YYYY.
When just converting dates to and from strings in VBA, the runtime environment will use your date format for parsing the string as well. It will be able to figure out the correct date from a string as long as the date-string is in the expected format.
The Jet-/Ace-Database-Engine, however, is stricter about dates. It will only recognize date-strings in either the US-Date-Format (MM/DD/YYYY) or the international ISO-Format (YYYY-MM-DD).
Now, if you are in the US, you could be lazy and just ignore this whole issue. By chance, your date will end up in a correct format in the string. - But you absolutely shouldn’t! - If you do, your application will break as soon as anybody is using it with a different date format configured for his (or her) computer.
To deal with this issue, I recommend to explicitly format any date value in the international ISO-Date-Format. – You could use the US-Format as well, but I strongly prefer the ISO-Format to reduce the visual ambiguity.
To achieve the desired formatting use the Format-Function for your date. The format function will return the date in a String data type formatted to your specification.
Dim sql As String sql = "SELECT * " & _ " FROM aTable " & _ " WHERE DateValue >= #" & Format(aDateVariable, "yyyy-mm-dd") & "# " & _ " AND DateValue < #" & Format(anotherDateVariable, "yyyy-mm-dd") & "#;"
You remember the hash sign as delimiter for Date/Time literals in Access/Jet-SQL? In the Query Designer, Access will add them automatically for you most of the time. When writing SQL Strings in VBA it’s your task to add them.
What about Time in SQL Queries?
If you explicitly want to include time in your SQL query criteria, it is less of an issue than the date itself. You simply use hh:nn:ss as additional format for your Date/Time value, where nn is the placeholder for minutes. This will result in an unambiguous 24hr time formatting that is understood by the Ace-/Jet-Database-Engine.
Dim sql As String sql = "SELECT * " & _ " FROM Table1 " & _ " WHERE DateTimeValue >= #" & Format(aDateTimeVariable, "yyyy-mm-dd hh:nn:ss") & "#;"
Dates in an International Environment
You might wonder, how to deal with dates if the users of your application are in different countries and use different date formats.
There is a simple rule that might save you a lot of trouble: Less is more!
VBA and Access will automatically use the regional settings to display and parse date and time values in your user interface. The less you fiddle with your date data entry and display the less trouble there will be on computers with different locale settings.
Use Predefined Formats Instead of Fixed Custom Formats
If you are in the US and set the display format of any date column or date displaying form field to the explicit format “MM/DD/YYYY” it will display exactly the same as setting it to the predefined, named format “Short Date”.
Now, if a user in another country (e.g. Germany) with another date format on his computer will open your database, your fixed format date controls will still display as “MM/DD/YYYY”. - This is very confusing for people not used to this format.
But if you had used the named format “Short Date” instead, the date will be displayed as “DD.MM.YYYYY” matching the expectation of the user from his cultural background.
And the matter is even worse, than apparent at first sight. Any date the user enters into this field will be parsed according to the date format in his Windows Regional Settings, not according to the explicit format you defined. In the above example, the German user has to enter the date in the “DD.MM.YYYY” format for it to be correctly recognized by Access/VBA even though the date is then displayed in “MM/DD/YYYY” format.
Always use predefined, named date and time formats instead of explicit custom format strings in your applications’ user interface.
All the same applies to Time in an international scenario. While time is displayed in most (all?) cultures as “hh:nn:ss” (nn representing minutes here), it might be displayed either as 12 hour time with an added AM/PM or it might be displayed as 24 hour time. If you follow the same rules as above and keep your hands of time formatting completely or only use named time formats (e.g. “Short Time”) you and your users will be fine.
When to use Explicit Date Formats?
You should use explicit formatting only for reporting or data exchange purposes where there are corporate guidelines on the date format to be used or there is a data file specification that defines the date format.
In printed (or PDF) reports the date formatting should rather match the expectations of the intended audience than the regional settings of the user creating the report. You can use the report controls’ Format-Property to define the output format of dates in your report. For time values you can explicitly force a 12hr AM/PM formatting by adding the “AM/PM” clock format component to the time format definition.
If your application needs to import or export Date/Time data from or to text files, you absolutely need to define a specification (or adhere to an existing one) for these text files. The specification must determine a date format that is to be used in those files. When implementing your import/export routines you should explicitly format (or parse) the date according to this specification.
In Access, you can define the date format for text file imports/exports and save them in Import- /Export-Specifications.
If you are exporting Date/Time data to a file format that has specific date data types, like dBase or Microsoft Excel files, it is once more advisable to keep your hands off and export the date data type without any formatting.
Data Entry Input Masks for Dates
Generally, I do not like rigid input masks for data entry. If your Access application is used only by users from one particular culture, they might be bearable. However, as soon as you want to accommodate user using different date formats in your application, input masks are even worse than usual.
An input mask in Access is pretty static. If you define a date input mask matching the date format of your country it will not at all adapt to a divergent date format. While surprisingly this does not too much damage when switching from DD.MM.YYYY to MM/DD/YYYY, apart from the wrong separator char displayed in the mask, it has obviously most annoying effects when the preferred date format of the user is YYYY-MM-DD.
I advise you apply my rule of less-is-more once again and refrain from using input masks altogether. If you still want to use input masks regardless, you to include code in your application that adjusts the input masks to match the date format of the current regional settings on the computer running your application.
There are lots of aspects in dealing with date and time data in Microsoft Access applications. Luckily, solutions for the really heavy lifting in common scenarios are already provided for in Access and VBA. You only need to know when and how to use the tools available.
I never imagined this article to be as long as it now is. I hope its length is justified by it being a complete and comprehensible guide for you to include robust and versatile date and time handling in your applications.
© 1999 - 2017 by Philipp Stiefel