Data Management

Grouping dates in Access by date components

You don't need a complex set of expressions or even VBA to sort Access date values by specific components, such as month or year. Use DatePart() and sort on its results.

When grouping date values at the query level, you can rely on the DatePart() function. This function evaluates a date value and returns a specific component, so you can use it to group by month, year, day of the week, and so on. It's a flexible tool that supports the following options:

Description of results Code Example based on 1/10/2011
Four-digit year yyyy 2011
Quarter value (1 through 4) q 1
Month value (1 through 12) m 1
Two-digit year y 11
Day value (1 through 31) d 10
Day of the week (1 through 7) w 2
Week of the year (1 through 52) ww 2

Working with date values in the Northwind Orders table (Northwind is the sample database that comes with Access), let's illustrate the use of DatePart() to group date values by year and month. First, to sort order dates by year, you'd use the following query expression:

SortByYear: DatePart("yyyy",[OrderDate])

The yyyy code returns just the year component from each date in the OrderDate field. In this case, there's not much to see because the date values are already in order by virtue of their data entry order. Adding a sort order will ensure that the year values group (sort) as expected. In addition, although I use the term group, this isn't a grouping task, not technically. A simple sort groups the values.

You can see a bit more action by sorting the dates by month values. To do so, use the month code with the same set of records. The results are very differently—orders from different years sort together to form groups by month:

SortByMonth: DatePart("m",[OrderDate])

Retaining the year-returning expression isn't strictly necessary; I left it so you could see more easily see the groupings by month, rather than year.

When applying this sorting technique to your own work, remember that Access gives precedence to columns from left to right. That's why I moved the year-returning expression to the right of the month-returning expression. This positioning behavior isn't always a factor, but usually it is.

In this example, the date argument refers to a date field, OrderDate. You can use a literal date value, but be sure to delimit using the # character, as follows:

DatePart("m",#1/10/2011#)

You might be wondering how this function differs from Format(). The returned data type is the biggest difference. Format() returns a string and Access will sort those results as a string. The DatePart() function returns an Integer value, which Access will sort numerically. This difference matters!

Putting it to use

Simple grouping by date component is just one use for dropping a DatePart() function into a query. You can expand on this technique by using the results as criteria. For instance, you might want to return only 1997 orders in the month of February. There's more than one way to do this, as is usually the case in Access, but the existing query sets up this situation nicely. Simply add the appropriate values, 2 and 1997, to the Criteria cell in each column. There are 29 records for February 1997.

You don't have to display the date component columns in your query. By unchecking the Show box in either or both columns, you can return filtered records without displaying the criteria you used.

A word on time

The DatePart() function also returns time components. Simply use the right code to group values by time:

h Military time

n Minute

s Second

That n is correct. The more likely code, m, is used to denote month.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox