Calculating with dates and times can be complicated. Access provides a number of helpful functions. These functions often use one of several text strings for the various time intervals that we are most often interested in.
Intervals- Values you can use for interval in a function (must include the quotes):
Date Part: Pull out part of a Date/Time
General Form: DatePart(interval, date) - interval is a text string from the list above for the part of the date you want to see and date is a field name or a calculated value that results in a date/time.
There are two optional parts to this function that are not likely to use very often:
DatePart(interval, date [,firstweekday [,firstweek)
firstweekday is the number of the day of the week that you want to use as the first day. The default is Sunday.
firstweek is the week number that you want to use as the first week of the year. The default value is the week that contains January 1.Examples:
DatePart("yyyy",[Birthdate]) will produce the year part of the Birthdate value.
DatePart("m",[Birthdate]) will produce the month part of the Birthdate value.
DatePart("d",Now()+30) will produce the day that is 30 days from today
DatePart("w",Now(),2) will produce the number of day of the week, starting with Monday as day 1.
DateAdd: Add to or subtract interval(s) from a Date/Time
General Form: DateAdd(interval, number, date)- Takes the date and adds the given number of intervals to it. A negative number will subtract that many intervals.
Examples:
DateAdd("m",2,[MyDate]) will add 2 months to the value in MyDate.
DateAdd("yyyy",-10,[MyDate]) will subtract 10 years from the value in MyDate.
DateDiff: Difference between two dates
General Form: DateDiff(interval, date1, date2) - Finds the number of intervals between date1 and date2. The value is positive if date2 is later than date1 and is negative date1 is the later date.
Like the DatePart fucntion, there are two optional parts to this function:
DateDiff(interval, date1, date2 [,firstweekday [,firstweek)
firstweekday is the number of the day of the week that you want to use as the first day. The default is Sunday.
firstweek is the week number that you want to use as the first week of the year. The default value is the week that contains January 1.Examples:
DateDiff("yyyy", [SoldDate],Now()) will show the number of years between today and the value in SoldDate.
DateDiff("d",[PurchaseDate], [ShippedDate]) finds the number of days between the two dates.
DateValue: Turn a text string date into a number
When a field for dates is set up as text and later you want to
calculate with that date, you need a function to turn the text into a
number.
General Form: DateValue(date) - returns the number that represents the text string date, like "October 14, 2000".
Examples:
DateValue("Sept. 11, 2001") returns the value 9/11/2001.
DateValue([DateGraduated]) returns the value 5/5/1999 when the field has the text value "May 5, 1999".
Format: Pattern for display a date/time
The Format function allows you to choose a standard pattern or create
your own pattern for how to display a Date/Time value. The Format
function can, of course, display only part of a date/time value. Don't
confuse it with the DatePart function, however. The DatePart function can only pull out one part of the date/time value, like the month or
day or year or hour.
General Form: Format(datetime, pattern) - takes the datetime value and formats it like pattern.
Examples:
Format(Now(),"dddd, mmm d yyyy") returns "Tuesday, Mar 7 2006" if the current date is 03/07/2006. Only the punctuation that you include in the pattern will show up.
Format(Now(),"h:m:s") return the time portion, like 13:23:45 if the current time is 1:23:45 pm.You can also use the standard date/time formats for the pattern:
| Format Pattern: | Produces: |
|---|---|
| General Date | 3/8/2006 10:39:59 AM |
| Short Date | 3/8/2006 |
| Medium Date | 08-Mar-06 |
| Long Date | Wednesday, March 08, 2006 |
| Short Time | 10:39 |
| Medium Time | 10:39 AM |
| Long Time | 10:39:59 AM |
Last updated: September 17, 2012