Sorting/filtering by year and then month

[Remster]

Folks



I need some help on how to work with dates in the following scenario.

I have a database of documents, each of which has a review date, e.g.

Document 1: 02/02/10 [dd/mm/yy]
Document 2: 12/12/09

The reviewer needs to be notified on the first of the month preceding the review date, so

Document 1: 01/01/10
Document 2: 01/11/09

I'm generating these notification dates using DateSerial(Year([ReviewDate]),Month([ReviewDate])-1,1). However, I can't get these dates to sort properly: Access is sorting them by day, then month, then year. I need them sorted by year, then month, then day.

How would this normally be handled?

Remster

[alansidman]

I would insert three fields into my query. Each is an expression.
I would enter them in the first three columns of the query.
First one. RYear: Year(ReviewDate)
Second Field. RMonth: Month(ReviewDate)
Third Field. RDay: Day(ReviewDate)

Set each to sort Ascending and make sure the check box is unchecked. This will assure proper sorting without displaying the three helper columns.

Alan