SQL - Query range between two dates (NON-VBA)

Question

I see various topics on this around stack overflow but none that fit the contect of MS-Access...

Given a starting date and an ending date, is there a way through SQL to return records for each given month within the time frame?

EG:

A record has a Start Date of #1/1/2010# and an End Date of #1/31/2010#

Running the query against that single record, I would like the results to be
            #1/4/2010#   (Monday the 4th)
            #1/11/2010#  (Monday the 11th)
            #1/18/2010#   ...etc
            #1/25/2010#

Restrictions

• MS-Access 2003 :No Case/Loops inside the SQL (IIF statements are good)
• This is a view only, NO VBA will be used since the data will not be tampered with. Disconnected recordset is my last option. I would prefer to find out theres some way to call your customized functions in the SQL to help return these values... some class stored on a global scope while you iterate through this date range maybe...

Is this possible? I see many no's, but if there was a way to pass a value into a function I could find a way to make this work. Sad that I don't have a way to simulate a stored procedure without using a d/c recordset, at least that I know of... any experts out there know a way?

Answer 1

You could create a giant calendar table, with dates and days in it. 4/12/10, Monday; 4/13/10, Tuesday, etc. Then query that giant table for the results. How big of range in the past and future do you need? Even though this is a clumsy solution, it would certainly make the queries clean and simple.

Answer 2

You are talking about a query that generates multiple records (one per date) out of a single record. I guess this should be made through a stored procedure (if your database is SQL) or through recordsets. No query will do the trick. Creating a recordset and adding records where needed seems quite straightforward to me. You should go for it.

Answer 3

Since this is probably related to reporting you could use temporary table approach, that is not so bad, especially if you might be running multiple reports against a given period (but do take care about multiple users).

Now, just for fun, here's a super-ugly, but semi-flexible approach (uses table with 10 entries and works for a range of 273 years)

Make a table called t10 with one field - id (make it long and primary key) and enter ten rows: 0,1,2,3..9

After that

SELECT #1/1/2010#+
       [t10].[ID]+
       [t100].[iD]*10+
       [t1000].[ID]*100+
       [t10000].[ID]*1000+
       [t100000].[iD]*10000 AS Mondays,
FROM t10, 
     t10 AS t100, 
     t10 AS t1000, 
     t10 AS t10000, 
     t10 AS t100000
WHERE  Weekday(#1/1/2010#+
               [t10].[ID]+
               [t100].[iD]*10+
               [t1000].[ID]*100+
               [t10000].[ID]*1000+  
               [t100000].[iD]*10000)=2;

returns all Mondays from 2010-1-1 to 2283-10-15 and is not as slow as it is ugly. Of course to get a solution for your question you'll have to filter the results for your fields date1 and date2.

This is in essence same solution as having temporary table, with the main difference that you don't have to rebuild the table on each query.

You could achieve the same result with only one table with entries 0..99999, and if i was to use temp tables I would probably use something like that.

Answer 4

I hope I understand your question correctly. To to this we need to create a cartesian product - so you should create another table. It only needs an ID in it with numbers that go from 1 to the highest number of weeks that your date range will contain.

For example table: tCounter(CounterID int)
CounterID
1
2
3
4
etc..

for test data I created a mock table with your data

For example: tDates(DateID int, startdate datetime, enddate datetime)
DateID, Startdate, EndDate
1,  1/1/10      1/31/10
2,  1/18/10     3/4/10
3,  2/1/10      2/28/10

the msaccess sql statement that will produce your results looks like

SELECT tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7) AS NewDay
FROM tDates, tCounter
WHERE (((((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7))<=[enddate]))
ORDER BY tDates.DateID, ((7-Weekday([startdate],3))+[startdate])+(([CounterID]-1)*7);

If you want to change the day from Monday to another day, change the argument in the weekday function. 1=Sunday->7=Saturday

e.g. Weekday([startdate],3)...the 3=Tuesday so result is Monday 
e.g. Weekday([startdate],5)...the 5=Thursday so result is Wednesday 

Hope that works for you.