Use a Cartesian product to create a list of all dates

Today's guest blogger is Access MVP Glenn Lloyd of Argee Services. Check out his Office help blog and Access Help and Tutorial Blog.

In working with applications that require scheduling, I often find a need for a list of all calendar dates within a specified time frame. Using Cartesian Product makes generating this list quite straightforward. The technique requires three tables. The first two for days and months are static once created and populated. The days table has a single field with 31 records. The field value ranges from 1 to 31. The months table also has only a single field with just twelve records with values ranging from 1 to 12.

The only table that requires ongoing maintenance in a production database is the years table. Again this table has only a single field with one record for each year of the time span you want to represent.

A single query and subquery is all that is needed to create the list of dates. The subquery uses the dateserial() function to calculate the date value for each possible combination  of days, months, and years from the three tables. Dateserial() is smart enough to return a valid date even though the arguments would ordinarily translate to an impossible date (2009,2,29, for example.) Unfortunately for the purposes of this tip, the function returns the next possible date after the date specified in its arguments when it encounters an impossible date. To fix this problem, the main query selects unique values from the subquery.

Here is what it looks like in SQL:

SELECT DISTINCT Dates.dtmDate
FROM (SELECT DateSerial([idYear],[idMonth],[idDay]) AS dtmDate
        FROM tblYears, tblMonths, tblDays) AS Dates
ORDER BY Dates.dtmDate;

Download a sample .accdb here.

Send your Power Tips to Mike and Chris at accpower@microsoft.com.