Obtain quarterly records in Access with the DatePart function

by Mary Ann Richardson | Apr 25, 2006 7:00:00 AM

Tags: Microsoft Office, Databases, Mary Ann Richardson, DatePart, Microsoft Access, Click New, cell, Microsoft Office Suite Access Tips Newsletter

Takeaway: How did your company's stock fare last quarter? Who came aboard during the first quarter last year? Mary Ann Richardson shows how to use the DatePart function in Access to obtain quarterly results.

IT managers often want to know data points about a particular quarter. If you need to retrieve this information, you can use a Between expression in a query's Criteria field to find all Access records between a range of dates, such as Between #4/1/2005# and #6/30/2005#. Another alternative is to use the DatePart expression, which lets you find all records for a quarter.

Let's say you need to obtain a listing of all employees who were hired the second quarter of 2005. To obtain the answer using the DatePart function, follow these steps:

1. Open the Employees database and click Queries under Objects in the Database Windows.
2. Click New and then OK.
3. Select the Employees table from the list, and then click Add.
4. Double-click the Employee ID, First Name, and Last Name fields from the Employees table field list.
5. Click in the next blank field cell and enter the following expression:
   Quarter: DatePart("q",[Hire Date])
6. Enter 2 in the Quarter field's criteria cell.
7. Click in the next blank field cell and enter the following expression:
   Year: Year([Hire Date])
8. Enter 2005 in the Year field's Criteria cell. Then, simply save and run your query.

You can also use the DatePart and Year expressions in a parameter query and allow users to enter the quarter and year themselves.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

Project Managermgessner@...  | 04/26/06

Varies from place to placeTony Hopkinson  | 04/26/06