Page 19 of 23PREVNEXT

Queries II: Limit your data and calculate values

 
Expression Purpose
PickUpTime: DateAdd("h", 3, [ArrivalTime]) Displays a time that is three hours after ArrivalTime.
Age: DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") ) Calculates someone's age from their birthday, relative to current date.
LagTime: DateDiff("d", [OrderDate], [ShippedDate]) Displays the number of days between the OrderDate and ShippedDate fields.
YearHired: DatePart("yyyy", [HireDate]) Displays the year each employee was hired.
MonthNo: DatePart("M", [OrderDate]) Displays the number of the month, as in 8 for August.
ThisMonth: Format(Now(), "mmm") Displays the abbreviated month that the current date represents, where mmm is Jan through Dec.
Some sample date expressions.

You can work with dates in calculations, such as subtracting one date from another or retrieving all dates that are earlier than today.

In Access, dates and times are stored as decimal numbers so that you can use them in mathematical equations. But for simple date calculations, you won't need to worry about what's happening behind the scenes because Access takes care of the math for you.

With Access, you can do more than just add and subtract dates. By using DatePart, you can display part of a date, such as the year someone was hired.

You can see some date expressions in the table to the left and in the Quick Reference Card at the end of this course.

Page 19 of 23PREVNEXT