ACC2000: Dynamic Query to Return Records for the Previous Month
ID: Q202319
|
The information in this article applies to:
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article demonstrates how to create a query that returns all the
records in which the value of a Date field falls within the previous month.
MORE INFORMATION
To create a query that returns the data from the previous month based on
the current date, use the Between...And operator with the DateSerial() function in the Criteria of the Date field. The DateSerial() function returns a date for a specified year, month, and day. The syntax of the DateSerial() function is:
DateSerial(year, month, day)
The following example returns all records from the Orders table of the
sample database Northwind.mdb in which the value in the ShippedDate field
falls within the previous month.
CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file
and perform these steps on a copy of the database.
- Start Microsoft Access and open the sample database, Northwind.mdb.
- Open the Orders table in Datasheet view.
- Click in the ShippedDate field.
- On the Records menu, point to Sort and click Sort Ascending.
- In several of the records in which the ShippedDate field is empty,
enter dates that fall within the previous month.
- Close the Orders table.
- Create the following query based on the Orders table:
Query: qryLastMonth
---------------------------------------------------------
Type: Select Query
Field: OrderID
Table: Orders
Field: OrderDate
Table: Orders
Field: RequiredDate
Table: Orders
Field: ShippedDate
Table: Orders
Criteria: Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)
- Save the query as qryLastMonth.
- Run the qryLastMonth query. Note that it returns only the records that you changed in step 5.
In this example, the DateSerial() function automatically handles the
change in the year. In the expression in the Criteria of the ShippedDate field, the second instance of the DateSerial() function sets the day argument to zero. Therefore, the expression returns the last day of the previous month. For example:
DateSerial(97,1,0)
returns
12/31/96.
REFERENCES
For more information about the Between...And operator, click Microsoft Access Help on the
Help menu, type "type Between...And" in the Office Assistant or the Answer Wizard,
and then click Search to view the topic.
For more information about the DateSerial() function, click Microsoft Access Help on the
Help menu, type "DateSerial" in the Office Assistant or the Answer Wizard,
and then click Search to view the topic.
For additional information about other expressions to manipulate dates, please see the following article in the Microsoft Knowledge Base:
Q210604 ACC2000: Functions for Calculating and Displaying Date/Time Values
Additional query words:
prior before earlier monthly
Keywords : kbdta QryHowto QryCrit
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto