List of Dates Between a StartDate and a FinishDate

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

I'd like to have a query that would generate a list of dates
between a starting date and a finish date, but I can't seem
to figure a way to do it!

Any ideas?
 
You can make use of a driver table: Call it Iotas, one field, its primary
key, iota, with values from 0 to, say, 999.


SELECT startDate + iota
FROM iotas
WHERE iota <= 1+ endDate - startDate


as example, or, making less assumptions:


SELECT DateAdd("d", iota, startDate)
FROM iotas
WHERE iota <= 1+DateDiff("d", startDate, endDate)



Vanderghast, Access MVP
 
Select your Date Field in your query.
on the "Total" line, Select "Where"
In the "Criteria" section, enter
Between [Enter StartDate] And [Enter EndDate]

Make sure you select the Date Field again in your query and in the "Total"
line, Select "Group By"
 
You can make use of a driver table: Call it Iotas, one field, its primary
key, iota, with values from 0 to, say, 999.
Click to expand...


Thanks for the reply, Michel, and sorry to be dim here...

Are you saying to actually enter records with values from 0,
to 999?
 
In a table called iotas, yes. Sure, you don't have to do it manually: doing
it in Excel then importing that long column in Access is a way to do it.

Such a table is a relatively small table, but it would be nice to make its
single field a primary key to have an index.


Vanderghast, Access MVP
 
Back
Top