Page 6 of 10PREVNEXT

Queries IV: Use functions and calculated fields in your queries

Ways to use dates in calculations


In addition to using numbers in calculations, you can also use dates and times. For example, you can subtract one date from another, or retrieve all dates that are earlier than today. You can use dates that way because Access stores them as sequential numbers, or serial dates.

Let's look at some examples:

Callout 1 Subtracting dates is a quick way to calculate elapsed time. In this case, subtracting May 5 from June 4 could tell you how long it took for a critical part to arrive.
Callout 2 You can use comparison operators along with the And and Or operators. This expression returns all dates before (less than) February 3 and after (greater than) February 8. In other words, you're excluding several days of records from your results.
Callout 3 This expression does the opposite. It returns records for dates greater than February 3rd and less than February 8th.
Callout 4 This expression uses the Between ... And operator and the Date formula to return all records between now and the previous seven days.

Those are just a few of the ways you can work with dates. You'll get a chance to try them in the practice session, and of course, links in the Quick Reference Card take you to more information.

Page 6 of 10PREVNEXT