How to Work with Dates and Times in Microsoft® Access Databases

On this page:

Last updated on 2024-05-02.

What's the Date

Consider what you understand the date to be when you read 24/09/2019. You and your computer are probably in agreement that 24/09/2019 refers to Tuesday 24th September 2019.

If you're working in the United States and you're guessing I'm a Brit writing this piece, you'd assume I meant 09/24/2019.

But what about interpreting 12/09/2019?

In adopting a blueprint for dates, a database developer should have in mind questions like these:

• Is Access's record of the input date the same date as the user wants recorded?
• Does the recorded date register as the same date with all users of the database?
• Do my queries work correctly if they include date filters or expressions based on dates?
• Will dates in reports produced by the database and sent to people outside the office be understood correctly by the recipients of those reports?

Your Computer's Region Settings

Much of Access's response to dates and times, is determined by the region to which the computer you're using is set. The setting is in Window 10's Control Panel: open Control Panel and navigate via Clock and Region —

Then choose Region to display the Region dialog box —

The settings shown above are for my computer, as supplied to me in the UK. I don't alter these default settings, for reasons I explain below. I illustrate them here because they have an influence on the results of some of the tests on Access's conduct of dates and times that appear below.

As a database developer, I never insist that a client adopts one set of Language Preferences over another. I believe I must make a database manage dates and times correctly irrespective of any tinkering a particular user may do to their Windows settings.

Access's Dates and Times

Access stores input into a Date/Time field as a floating point number; that is, a number with an integer part and a decimal part.

The integer part of a number stored in a Date/Time field relates to Access's built-in calendar that runs from January 1st 100 CE to December 31st 9999 CE. Access stores January 1st 100 CE as the number -657434; December 31st 9999 CE, as 2958465.

Count one for each day since January 1st 100 CE to the date you have in mind and you have the number Access stores representing that date. Count 43843 and you'd be at January 13th 2020.

The decimal part of a number stored in a Date/Time field represents time as a fraction of 24 hours. 0.66666 represents 3:59:59 PM — very nearly two-thirds of the way through a day, at 15 hours, 59 minutes and 59 seconds. Add another decimal place — 0.666666 — to achieve 4:00:00 PM exactly.

A number with digits to the left and right of the decimal point is how Access stores date and time together. For example, 44790.25 is August 17th 2022  6:00:00 AM.

Exploring Dates and Times in Access

You can use the Immediate Window in Access's Visual Basic Editor to test how Access works with dates. Copy and paste any of the examples below into your Immediate Window and then press Enter to see the result.

The Format(date,format) function displays the value assigned to its first argument — in this case the 44790.25 — in the format assigned to its second argument.

The result appears as I'd expect it to because the format I assigned follows the Language Preferences set on my computer.

What if I muck about with the assigned format?

Access isn't fussed by a change to the format. Formatting is cosmetic — for appearance purposes only. What matters to Access is that the date is 44790.25.

Here we've omitted the seconds and AM/PM components:

Format adjusts to the 24 hour clock as appropriate:

Note that minutes are indicated by n to distinguish them from m for months.

Here are some examples to demonstrate the flexibility of the Format function:

dddd; and mmmm; causes Format to spell out days and months in full; ddd and mmm, the first three letters only.

Date and Now Functions

Access's and VBA's Date() function returns the current date determined by your computer's clock:

Date() returns a whole number — no decimal places. We learn that Access considers the start of the day to occur at midnight.

The Now() function returns current date and time as determined by your computer's clock:

Try that example a couple of minutes later:

14/01/2020 04:59:42 PM

DateSerial Function

Access's and VBA's DateSerial(year,month,day) function provides a means of constructing a date (that is, the number that Access uses itself to represent a number) out of values assigned individually to the year, the month and the day, in that order. This in the Intermediate Window of my computer:

Access has used the preferences set on my computer to format the result as a short date. We can demonstrate that DateSerial is returning a number, as we would anticipate:

Testing for any time component, we learn DateSerial assumes none:

Dates and Times at Point of Input

If you set the Data Type property of a field in an Access table to Date/Time, then Access will reject your attempt to input into that field anything that it fails to interpret as a date or as a date and time.

If you input into that field a date, or a date and time, in a pattern that doesn't match that in your Region Settings, then Access will exercise initiatives in interpreting your input.

In my experience, a user will input dates and times in the way in which he is habituated. He may use the Date Picker set against a text box, if the Picker is available; or he may type “24/9/2019”, “24/9”, or “24/9/19” or any other conceivable arrangement of day, month, year and separation characters. Or he may copy and paste.

There are two man issues to address:

• That the user sees the date and time that Access is recording as a result of her input
• That a stored date and time is what the user intends and not a spurious one resulting from Access misunderstanding the input.

Hence the two sections that follow.

On-Screen Display of Dates

Let's assume that the user is inputting into a text box on a form; and assume that she's inputting using whatever technique she chooses. We'll use this snip taken from her Invoice form to illustrate the effects various settings in her form have on the display of the input date:

The lower text box is named “tebInvoiceDate”. tebInvoiceDate has as its Control Source the InvoiceDate field in the table storing details of invoices. The Data Type of the InvoiceDate field is set to Date/Time.

These three properties of tebInvoiceDate have all been left blank: Format, Default Value and Input Mask.

Everything is in harmony: Access has stored the date as January 6th 2020, just as the user intended it to be.

Now, some misguided individual chooses to add their own design touches to the form and assigns mm/dd/yyyy to the Format property of the tebInvoiceDate text box:

The displayed Invoice Date has adopted the mm/dd/yyyy format applied by the vandal to the text box in preference to the default dd/mm/yyyy of the computer's Regional settings.

😕 Surely this is likely to cause the user confusion …

😡 … and frustration as the user fiddles achieving a date that looks right and in so doing stores one that is wrong.

To avoid any confusion, frustration and mistakes, my approach is either to leave the text box's Format property blank or to select a format from the list in the Format property's drop-down list:

I'll qualify that approach by recommending that the year is always displayed as four, not two, digits. This is a personal preference that comes out of experiences with databases that store dates from years ago or in the future.

For example, the record of an employee in an HR database. His date-of-birth input as “20/2/49” is meant to be February 20th 1949 — not February 20th 2049 as the database has stored it:

An example from another database: the user input what they intended to be November 19th 2008 by typing “19/11/08”. Are you guessing correctly what Access made of this?

The # characters indicate to the Format function that the value it is evaluating is a date.

You could devise a set of rules based on the concerns raised above:

• Don't fiddle with a computer's Regional settings in Windows
• Don't apply customised formats to controls on forms that display dates
• Do if there's a need to apply date format to a control, pick one from the drop-down list in the control's Format property. The same applies to controls for the input of time and of date and time
• Do display the year as four digits, never two.

Storage of Dates and Times

In the design of tables, the developer has the option of whether or not to assign a format to any Date/Time field. I've experienced no great advantage in applying a format.

What's more, I've received databases for conversion and upgrading in which formats applied to Date/Time fields have caused unnecessary work checking that they did not interfere with the intended running of forms, queries and reports.

Hence my recommendation:

• Don't apply a format to any Date/Time fields in tables.

Default Date and Time Settings

You can set the Default Value property of a control on a form and of a field in a table.

You may consider it helpful to the user to pre-populate a Date/Time field with today's date in a new record for, say, the receipt of an order. In such a case use the Date() function as the default.

Apply the Now() function only when you specifically need time recorded. In the section about date and time filters in queries, below, I explain why it's inadvisable to use Now() when it's date-only that you want recorded.

Validation Rules

In a Date/Time field in a table, Access stores the date Thursday 23th January 2020 as the number 43853. It stores 6:00 AM on Thursday 23th January 2020 as 43853.25.

One way to guide a user to input a date with no time component is to assign a rule to that fields Validation Rule property. If the field in question is named “DrawdownDate”, then this is one expression you could assign to its Validation Rule:

If a user inputs anything that Access considers has a time component, then Access will prevent the user leaving that field until he has adjusted his input to a date alone, no time.

If the user is to be allowed to leave that field without any data value, then the Validation Rule needs extending:

Adding a message like “Please input only a date, no time” to the field's Validation Text property will spare the user from incomprehensible messages from Access when he transgresses your validation rule.

Dates in Reports

dd/mm/yyyy and mm/dd/yyyy have always been popular format properties. OK if your database is used exclusively locally. No so if your database is outputting reports as PDFs that you're sending to recipients in other parts of the world.

Potentially even more confusing are the dd/mm/yy and mm/dd/yy formats. To what date in a report title attached to an email you receive does “20/04/20” refer, do you assume?

Surely a format that conveys the date as the same to everyone is a sensible approach to adopt: dd mmmm yyyy or dddd dd mmmm yyyy. Or the ISO 8601 recommended yyyy-mm-dd, for example:

Date Filters in Queries

Consider a query named qsDeals which selects deals from a table named tblDeals:

Unfiltered, qsDeals produces a data sheet listing Deals for the year 2019:

Now let's apply a filter for Deals made on Wednesday 9th October 2019. Note that to include a date in a filter, the date must be prefixed and suffixed with a # character:

All good because the date in the filter follows the Short-Date Regional setting — dd/mm/yyyy — of the computer in use:

What you, as a developer, have to bear in mind is the result of this query when it's run on a computer with Short-Date Regional settings of mm/dd/yyyy. You're going to get Deals for Tuesday 10th September 2019.

To avoid situations like this, you could consider the use of Access's DateSerial(year,month,day) function. The filter for October 9th 2019 would be DateSerial(2019,10,9), thus ensuring no uncertainty as to which date is operative, whatever Regional settings are in force.

Expressions in Query Filters for Dates

Used for filtering dates in a query, the DateSerial(year,month,day) function will accept expressions. In this example running the query presents the user with a message asking for the month for which records are to be filtered:

>=DateSerial(2019,[Month number?],1) And <DateSerial(2019,[Month number?]+1,1)

This makes the query general purpose because the user can choose the month without needing to modify the query. Should you want the year filtered as well:

>=DateSerial([Year?],[Month number?],1) And <DateSerial([Year?],[Month number?]+1,1)

Resulting in these two messages to the user:

If you copy and paste the above filter, make certain that in your query it is all in the same row of the same Criteria cell. When on that cell, Shift + F2 opens the Zoom dialog box, which makes your criterion easy to work on:

Warning If you're filtering on a date field into which both date and time have been input, read the next topic!

Filtering Date and Time

Consider the query qsDeals based on a table into which users have input a mix of dates and dates-and-times into the table's DrawdownDate field. Here's the data set correctly filtered for September 1st 2019 (UK):

Now see the result of applying the simple filter #01/09/2019# (UK date format) to the same data:

If this result is one you weren't expecting, you're not the only one. I've come across a number of reports meant to present daily information.

Those reports were totally inaccurate, simply because their developers had failed to make provision for the way in which Access stores dates and therefore how dates should be queried.

Similar failings I've be asked to fix occurred in reports based on times, e.g. call-logging modules in databases intended (supposedly) to report calls ordered by shift.

Beware Between

At first sight the use of the Between……And…… expression might seem an ideal way of filtering across a range of dates. For example, Between #01/09/2019# And #30/09/2019# to deliver September 2019's records.

Ask yourself what that filter will return when records contain both date and time. Will it include or exclude September 30th 2019 10:30 AM from the results?

On all occasions, I favour the >=……And……<=…… construction over Between……And…… .

Another caution over Between, when you receive a request such as “A report, please, on all orders valued between ₤5,000 and ₤10,000”.

Always best to ascertain precisely what is required of the report. Is an order of value ₤5,000.00 to be included? Does a ₤10,000.00 order belong in the requested report or in another for “high-value orders”?

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —