ACC: Dynamic Query to Return Records for the Previous Month

ID: Q176645


The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97


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 (or NWIND.MDB in version 2.0) in which the value in the ShippedDate field (or Shipped Date field in version 2.0) falls within the previous month.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.
  1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.mdb in version 2.0).


  2. Open the Orders table in Datasheet view.


  3. Click in the ShippedDate field (or Shipped Date field in version 2.0).


  4. On the Records menu, point to Sort and click Sort Ascending. (In version 2.0, point to Quick Sort on the Records menu, and then click Ascending.)


  5. In several of the records in which the ShippedDate field is empty, enter dates that fall within the previous month.


  6. Close the Orders table.


  7. 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) 

    NOTE: In Microsoft Access 2.0, there is a space in the field names Order ID, Order Date, Required Date, and Shipped Date.


  8. Save the query as qryLastMonth.


  9. 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, search the Help Index for "Between...And operator."

For more information about the DateSerial() function, search the Help Index for "DateSerial."

For more information about other expressions to manipulate dates, see the following article in the Microsoft Knowledge Base:

Q88657 ACC: Functions for Calculating and Displaying Date/Time Values

Additional query words: prior before earlier

Keywords : QryHowto QryCrit
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: April 23, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.