10 tips for working with dates in Microsoft Access

This article is also available as a PDF download.

Working with dates in Access can be somewhat confusing. Fortunately,
Access offers the Date/Time data type, which greatly simplifies the job of
handling dates. In addition, you’ll find a number of functions that manipulate
date values and components. But even with all the built-in help, solutions are
seldom intuitive. Here are a few tips that will help you solve some of the
most common date problems.

#1: Date data entry shortcuts

You don’t always have to enter a literal date value. Access
offers a couple of keyboard shortcuts, which are quicker and help eliminate
typos. To enter the current date, press [Ctrl] ; (semicolon). If you want to
copy a date from the previous record, press [Ctrl] ‘ (apostrophe). To enter the
current time, press [Ctrl] : (colon).

#2: Eliminating date data entry–sometimes

Sometimes a date is the current date, and sometimes it
isn’t. When a date is usually the current date, you can eliminate entering any
value at all. Simply set the field’s Default Value property to Now() or Date().
When you enter a new record, the property will enter the system’s current date
(and time). You don’t even need to press [Ctrl] ;.

#3: Now() versus Date()

The Now() function returns the system’s current date and
time. It’s a package deal–you always get both. If all you need is the current
date and not the time, use the Date() function instead. On the other hand, if
all you want is the current time, use the Time() function. Don’t use Now()
unless you really mean to store both the date and time.

Why does it matter? A value that includes both a date and
time component usually responds differently to expressions. For instance, you
might expect the following expression to find dates that fall after December
12, 2006:

WHERE field >#12/12/2006#

However, the resulting recordset
will include December 12, 2006, entries when the entry includes a time value. Conversely,
Access won’t return December 12, 2006, dates if the entry stores just a date
value.

#4: Auditing changes by adding an edit date

If you have multiple users updating data, you might want to
track who’s making changes. You can do so by adding a simple event procedure in
the following form to a data entry form’s Dirty event:

Private Sub Form_Dirty(Cancel As Integer)
editfield = Now()
End Sub

where editfield represents the field that stores the
last edit date.

Access fires the form’s Dirty event when the contents of the
form changes. This happens even if the user re-enters the same entry. If the
user catches the mistake, he or she can press [Esc] to cancel the change. However,
the user must then press it a second time to delete the newly inserted edit
date before leaving the record. Otherwise, Access will save the edit date.

#5: Calculating Julian dates

A Julian date identifies a date by its numeric rank since
January 1 (relative to the current year). This rank tells us how many days fall
between a date and January 1 of the same year. For instance, January 1 of any
year is 1, February 1 of any year is 32, and so on. Leap years complicate
things because dates following February 28 in a leap year aren’t the same as
those in a non-leap year. To calculate a Julian date, use the following
expression:

JulianDate = date - DateSerial(Year(date), 1, 1) + 1

where date is the
date or Date/Time field for which you’re calculating a Julian value. This
expression works for leap and non-leap years.

#6: Date delimiters

When including numeric values in expressions, you don’t need
to identify the value in any way. However, you must identify dates. The correct
character to use when including a literal date value is the pound character
(#). For instance, the following expression will not return the number of days between two dates:

TotalDays: 3/3/2007 - 2/1/2007

Instead, it returns -4.9825610363727E-04. That’s because
Access evaluates the dates mathematically: 3 divided by 3 divided by 2007,
subtract 2, and so on. The returned value isn’t wrong; it just doesn’t reflect
your intentions.

Be sure to identify date values so Access can handle them
properly in equations, as follows:

TotalDays: #3/3/2007# - #2/1/2007#

When you identify the values as dates using the # delimiter,
Access returns the value 30–the number of days between February 1, 2007, and
March 3, 2007.

#7: Using comparison operators to span a date range

Finding all the records with a specific date is easy–simply
enter the date in question. But finding a subset of dates that fall between two
dates requires the following conditional operators: <, <=, >, and
>=. For instance, the SQL expression for locating all date values earlier
than a specific date is:

WHERE datefield < #date#

If you’re using the QBE gird, you’d enter the simpler
expression into datefield‘s
Criteria cell:

< #date#

Perhaps you need a smaller subset, such as all the dates
before 2006 but after 2002. In that case, you’d specify both dates as follows:

WHERE datefield > #12/31/2002# AND datefield < #1/1/2006#

Alternatively, use the simpler expression via the QBE grid:

> #12/31/2002# AND datefield < #1/1/2006#

Both expressions return only those dates that fall in 2003,
2004, and 2005.

#8: Using Format() to display dates

Most of the time, you’ll want to display dates in a specific
format without storing that format as part of the date. The Format() function
allows you to display individual and combination date components without affecting
the stored date value. To display a single component, use the appropriate
format code. For instance, the following expression would return only the day
of the month as an integer (1 through 31):

Format(datefield, "d")

If you want a leading zero, use dd instead of d. The table below contains more
individual component formats.

When using Format(), remember that the resulting value is a
string. You’ll use Format() to display dates, but don’t use it when you must
use the results in mathematical equations.

#9: Sorting by date components

Sorting by dates is simple. You just set the date field’s
Sort option or add an ORDER BY clause to the SQL statement. However, Access
will use the entire date entry. If you’re trying to sort by a single date
component–the day of the month, the month, or the year–use the Day(),
Month(), or Year() functions. For instance, if you wanted to find all the
January dates, you’d use the SQL WHERE clause in the form:

WHERE Month(datefield) = 1

To enter the equivalent expression in the QBE grid, you’d
enter the following expression as datefield‘s criteria:

Month(datefield) = 1

#10: Returning the first or last day of the month

None of the Access date functions returns the first or the
last day of a specific or relative month. To do both, you’ll need custom
expressions. To return the first and last day of the current and relative
months, use the following expressions:

Current month

FirstDay = DateSerial(Year(date), Month(date), 1)
LastDay = DateSerial(Year(date), Month(date) + 1, 0)

Previous month

FirstDay = DateSerial(Year(date), Month(date) - 1, 1)
LastDay = DateSerial(Year(date), Month(date), 0)

Next month

FirstDay = DateSerial(Year(date), Month(date) + 1, 1)
LastDay = DateSerial(Year(date), Month(date) + 2, 0)