MS-Access / Getting Started

Date Functions

You've already seen how you can use simple addition and subtraction with dates. However, you can accomplish a whole lot more with some of Access's date functions.

Without a doubt, everyone's favorite date functions are Now( ) and Date( ). These functions grab the current date and time, or just the current date. You can use these functions to create queries that work with the current year's worth of orders.

Here's a filter condition that uses Date( ) to select projects that are past due:

=<Date()

Add this to the Criteria box for the DueDate field, and you'll see only those records that have a DueDate that falls on or before today.

Date logic becomes even more powerful when paired with the DatePart( ) function, which extracts part of the information in a date. DatePart( ) can determine the month number or year, letting you ignore other details (like the day number and the time). Using DatePart( ) and Date( ), you can easily write a filter condition like this one, which selects all the orders placed in the current month:

DatePart("m", [DatePlaced])=DatePart("m", Date( ))
 And DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date( ))

This rather lengthy expression's actually a combination of two conditions joined by the And keyword. The first condition compares the month of the current date with that of the date stored in the DatePlaced field:

DatePart("m", [DatePlaced])=DatePart("m", Date())

This expression establishes that they're the same calendar month, but you also need to make sure it's the same year:

DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date())

The string manipulation functions are designed with characters in mind. They can count letters, but they don't have any understanding of words and sentences.

One way you can get around this limitation is to use the unusual Instr( ) function, which searches for one or more characters inside a text value. (The name Instr( ) is short for "in string", because you're looking for specific characters inside a text string.) To search for the characters "he" in the text string "Hi There," you'd use Instr( ) like this:

Instr("Hi There", "he")

The result's 5, because the text "he" begins in the fifth character position. If Access can't find a match, then Instr( ) returns a result of 0. If there are multiple matches, then Instr( ) gets the first.

On its own, Instr( ) isn't terribly useful for filter conditions or calculated fields. However, you can use it in combination with other functions, like Mid( ) and Left( ), to snip out a part of a string near another letter. You could use Instr( ) to search for the first space, and take all the text before that space. In this way, you end up extracting an entire word.

Here's a slightly mind-bending calculated field that gets the first word from a ProductName field, using nested functions. (It's split over several lines here to fit the page. When you type it in, you'll put the entire expression on one line.)

FirstWordProduct:
  Left([ProductName], Instr([ProductName],
  " " - 1))

This expression translates as "find the position of the first space, subtract one, and take that many characters from the left of the text." Run this on a field with the value Banana Cream Fudge, and you'll wind up with the truncated text Banana, which makes for an impressive party trick.

The trick to using DatePart( ) (and several other date functions) is understanding the concept of date components. As you can see, using the text m with the DatePart ( ) functions gets the month number, and using the text yyyy extracts a four-digit year. Table-5 shows all your options.

Table-5. Date Components
ComponentDescriptionValue for February 20, 2006 1:30 PM
yyyyYear, in four-digit format2006
qQuarter, from 1 to 41
mMonth, from 1 to 122
yDay of year, from 1 to 365 (usually)51
dDay, from 1 to 3120
wDay of week, from 1 to 72
wwWeek of the year, from 1 to 528
hHour, from 1 to 2413
nMinute, from 1 to 6030
sSecond, from 1 to 600

Calculations with Dates and Times

When using date functions, you always need to be mindful of dates that include time information. (Remember, all date values can include time information. However, you tell Access whether or not to show the time component of a date, and let people enter it by choosing the right format for you date field. Most of the time, you'll use a format that hides any time information.)

Here's the issue: The Date( ) function returns the current date with a time value of 0. In other words, if the current date's July 4, 2008, the Date( ) function gives you the very first second of July 4, 2008the moment when the clock hit 12:00 a.m. (midnight).

If you aren't storing time values, this issue isn't important, because all your dates have a time value of 0. But consider what happens if you use the General Date format for your DueDate, which lets users enter both date and time information. Now the =<Date( ) filter expressions has a slightly different meaningit tells Access to match all the fields that were due on or before the first second of the current day. This filter expression doesn't match a record with a due date of 4:00 p.m. today.

In this situation, you probably want to change the filter expression to this:

<(Date( )+1)

Date( )+1 is tomorrow. In other words, this filter matches any records that have a due date that falls before the first second of tomorrow.

Incidentally, Access also has a function named Now( ) that gets the current date and time. So this filter expression matches any records that were due at the current time (of the current day) or any time and any day before that:

=<Now()

You use the date components with several date functions, including DatePart( ), DateAdd( ), and DateDiff( ). Table-6 has these and more useful date-related functions.

Table-6. Functions for Dates
FunctionDescriptionExampleResult
Date( )Gets the current dateDate( )1/20/2006
Now( )Gets the current date and timeNow( )1/20/2006 10:16:26 PM
DatePart( )Extracts a part of a date (like the year, month, or day number)DatePart(#1/20/2006#, "d")20
DateSerial( )Converts a year, month, and day into an Access date valueDateSerial(2006, 5, 4)5/4/2006
DateAdd( )Offsets a date by a given intervalDateAdd ("yyyy", 2, #22/11/2006#)22/11/2008
DateDiff( )Measures an interval between two datesDateDiff("w", #10/15/2006#, #1/11/2007#)12
MonthName( )Gets the name that corresponds to a month number (from 1 to 12)MonthName(1)"January"
WeekdayName( )Gets the name that corresponds to a weekday number (from 1 to 7)WeekdayName(1)"Sunday"
Format( )Converts a date into formatted text (using any of the date formatsFormat (#27/04/2008#, "Long Date")"April 27, 2008"
[Previous] [Contents] [Next]