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| Component | Description | Value for February 20, 2006 1:30 PM |
|---|---|---|
| yyyy | Year, in four-digit format | 2006 |
| q | Quarter, from 1 to 4 | 1 |
| m | Month, from 1 to 12 | 2 |
| y | Day of year, from 1 to 365 (usually) | 51 |
| d | Day, from 1 to 31 | 20 |
| w | Day of week, from 1 to 7 | 2 |
| ww | Week of the year, from 1 to 52 | 8 |
| h | Hour, from 1 to 24 | 13 |
| n | Minute, from 1 to 60 | 30 |
| s | Second, from 1 to 60 | 0 |
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| Function | Description | Example | Result |
|---|---|---|---|
| Date( ) | Gets the current date | Date( ) | 1/20/2006 |
| Now( ) | Gets the current date and time | Now( ) | 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 value | DateSerial(2006, 5, 4) | 5/4/2006 |
| DateAdd( ) | Offsets a date by a given interval | DateAdd ("yyyy", 2, #22/11/2006#) | 22/11/2008 |
| DateDiff( ) | Measures an interval between two dates | DateDiff("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 formats | Format (#27/04/2008#, "Long Date") | "April 27, 2008" |