Microsoft Access - Grouping Months In A Totals Query (Access 97/2000/2002)


If you've ever tried to group date values in a totals query, you probably used something similar to the Format() function to group by month and year, or perhaps even just the Month() function. Both of these pose problems, however. The Format() function returns a String value, not a date. This makes it difficult to sort the results in any meaningful way without resorting to odd date formats like 200308 or something of the sort. Of course, the Month() function returns a numeric value, but what happens if your data extends over a period of years? Using this function places all records of the same month into the same group regardless of the year.

Go back