[AccessD] SQL: Dynamic list of dates

Hi all

Just a reminder should you need to create a list of dates for a period, present or future, in pure SQL.
Here is how to create a dynamic list of dates for up to one year from any date.

Create two small tables, tblDays with Integer values from 1 to 31, and tblMonth with Integer values from 1 to 12.
Use these in a query with a Cartesian (multiplying) join as shown.

<sql>
PARAMETERS 
  Date1 DateTime, 
  Date2 DateTime;
SELECT DISTINCT 
    DateSerial(
      Year([Date1]),
      Month([Date1])-1+[tblMonth]![Month],
      Day([Date1])-1+[tblDays]![Day]) AS 
  DateOfPeriod
FROM 
  tblDays, 
  tblMonth
WHERE 
  DateSerial(
    Year([Date1]),
    Month([Date1])-1+[tblMonth]![Month],
    Day([Date1])-1+[tblDays]![Day])
  Between 
    [Date1] 
    And 
    [Date2];
</sql>

A note: If you prefer, you can of course use tblDays with an alias for tblMonths as well by filtering on 1 to 12, thus using one table only. In that case a more neutral name than Day should be given the field holding the values.

Or you could create a general purpose query, qdyMonth:

SELECT
  [Day] As Month
FROM
  tblDays
WHERE
  [Day] Between 1 And 12

and use that for tblMonth.

/gustav