Working with dates in Access can be 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 help you solve some of the most common date problems.

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).

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] ;.

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.

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.

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.

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.

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.

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.

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

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)

If you require additional information or assistance with this item, please give us a call.