CMIIandExcel
Board Regular
- Joined
- Sep 4, 2009
- Messages
- 190
Hi all
I am hoping for some help in incrementing through dates using VBA.
I am using VBA to fire queries at a database bringing back information by Month in a recordset.
The servers at work are struggling and are timing out due to the size of the record set being returned (one of the RS fields is being populated from a blob field in the db)
To get around this (the is no way I can get IT to solve the timeout on the server ) i need to make my RS smaller by breaking them down into smaller date parts.
My query uses a start and finish date and brings back results which are inclusive of the dates.
I need to break the queries down into 7 day increments (possible less) but the last block of each month should not over run into the next.
E.G
For Nov 2006 I would need to run the following Queries (inclusive dates)
Weds 1st - Tue 7th (7 days)
Weds 8th - Tue 14th (7 Days)
.............. (7 days)
Weds 29th - Thurs 30th (2 Days)
The next set of quries would have to start back on the 1st of Dec 2006.
The code would also need to handle moving through multiple years.
Any thoughts (on topic ones) or help would be greatly appreciated.
Regards
Mike
I am hoping for some help in incrementing through dates using VBA.
I am using VBA to fire queries at a database bringing back information by Month in a recordset.
The servers at work are struggling and are timing out due to the size of the record set being returned (one of the RS fields is being populated from a blob field in the db)
To get around this (the is no way I can get IT to solve the timeout on the server ) i need to make my RS smaller by breaking them down into smaller date parts.
My query uses a start and finish date and brings back results which are inclusive of the dates.
I need to break the queries down into 7 day increments (possible less) but the last block of each month should not over run into the next.
E.G
For Nov 2006 I would need to run the following Queries (inclusive dates)
Weds 1st - Tue 7th (7 days)
Weds 8th - Tue 14th (7 Days)
.............. (7 days)
Weds 29th - Thurs 30th (2 Days)
The next set of quries would have to start back on the 1st of Dec 2006.
The code would also need to handle moving through multiple years.
Any thoughts (on topic ones) or help would be greatly appreciated.
Regards
Mike