Select all dates between two dates (AC2007)

[AOB]

Hi guys,

I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)

I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date

So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.

Is there a way to do this?

So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :

01-Apr-2015
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-Apr-2015
​

(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)

Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?

Thanks!

Al

 

[JHB]

.. Or can you only query something which exists somewhere in a table?

Yes - but you can have some code which fill a table with the dates between Start- and End-date.

 

[AOB]

Gotcha - thanks JHB - so basically I have to make use of a temporary table in order to do this? Rather than doing it all in a query / subqueries?

 

[namliam]

Or you can make a perminant table, which holds values 1 thru 100
Then simply add them to your startdate as required untill you get to your end date.

Obviously 100 will be enough for a 3 month period, 180 for 6 months,e tc.

 

[AOB]

Nice suggestion namliam, wouldn't have thought of that

I ended up just building a function to loop through each date in the range and perform the calculation for each one. Was worried it might be slow (hence was hoping to find a SQL method) but it's actually strangely quick.

So it'll do!

Thanks for all your help, as always, fellas