databasedev.co.uk - database solutions and downloads for microsoft access

Show only Microsoft Access query records from the last 'n' months:

Using an expression in a Microsoft Access query will enable you to find all records stored in the table that include a date recorded within the last 'n' amount of months. This will be helpfull when you wish to view all orders placed in the specified period prior to todays date.

You can structure the query using the following expression:

• Field : OrderDate
• Criteria: >=DateAdd("m",-3,Date())

This criteria will show only records where the date in the OrderDate field is greater than or equal to Today's Date minus 3 months.

The example below shows the query in design view:

Query design showing expression to return records from the last 3 months

Running this will return only those records where the OrderDate meets the criteria specified.

This criteria can be altered to specify the time constraints required.

The DateAdd function uses the following arguments:

DateAdd(interval, number, date)

The interval argument has these settings:

Setting	Description
yyyy	Year
q	Quarter
m	Month
y	Day of Year
d	Day
w	Weekday
ww	Week
h	Hour
n	Minute
s	Second

You can use the DateAdd function to add or subtract a specified time interval from a date. For example, you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now.

To add days to date, you can use Day of Year ("y"), Day ("d"), or Weekday ("w").