Grouping dates in Access by date components

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:

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.