VBA Date Increment by week

[CMIIandExcel]

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

 

[VishMishra]

this will return the next week date to the date what you have given in the below Code:

DateAdd("ww", 1,"your date here")

 

[CMIIandExcel]

Vish

Thanks for the code: I am fine at incrimenting the date its managing the part weeks at the end of a month and also the roll over through years Im stuck on.

I am suffering a case of man flu so my head isnt the clearest

Any thoughts on tackling the end of month part week?

Mike

 

[VishMishra]

Hi Can you explain the question please...unable to understand

 

[CMIIandExcel]

Vish

The code will increment through from the first day of a month in 7 day steps returning a start date and an end date for that period(week). When the code comes to the end of the month I.E. not a full 7 days. it will return only what is remaing in the month.

The code would need to manage going from one year to the next.

hope that helps

Mike

 

[AlphaFrog]

Try something like this...

[color=darkblue]Sub[/color] Date_Increment_by_week()

    [color=darkblue]Dim[/color] dStartDate [color=darkblue]As[/color] [color=darkblue]Date[/color], dEnd[color=darkblue]Date[/color] [color=darkblue]As[/color] [color=darkblue]Date[/color]
    [color=darkblue]Dim[/color] dStartMonth [color=darkblue]As[/color] Date, dBeginingOfMonth [color=darkblue]As[/color] Date, dEndOfMonth [color=darkblue]As[/color] Date
    [color=darkblue]Dim[/color] Number_of_months [color=darkblue]As[/color] [color=darkblue]Long[/color], mo [color=darkblue]As[/color] [color=darkblue]Long[/color]

    dStartMonth = #11/1/2006#   [color=green]'Day is assumed to always be the 1st of the month[/color]
    Number_of_months = 2
    
    [color=darkblue]For[/color] mo = 1 [color=darkblue]To[/color] Number_of_months
    
        dBeginingOfMonth = DateAdd("m", mo - 1, dStartMonth)
        dEndOfMonth = DateAdd("m", mo, dStartMonth) - 1
        
        [color=darkblue]For[/color] dStartDate = dBeginingOfMonth [color=darkblue]To[/color] dEndOfMonth [color=darkblue]Step[/color] 7

            dEndDate = Application.Min(dStartDate + 6, dEndOfMonth)
            
            [color=green]'Display dates for testing[/color]
            MsgBox "Start date: " & dStartDate & vbCr & _
                   "End Date:  " & d[color=darkblue]End[/color]Date, , "Date Increment by Week"
                   
        [color=darkblue]Next[/color] dStartDate
        
    [color=darkblue]Next[/color] mo
    
End [color=darkblue]Sub[/color]

 

[VishMishra]

Hi,

The code, which i have given will return complete 7 days only even on month end or year end.

DateAdd("ww", 1, "28/03/2012")

Is this what you want? or at Month End you want to show only those days which are left in that month.

Like in the above example, you want to return the date "31/03/2012" or 04/04/2012?

 

[CMIIandExcel]

Just to return the 31-3-2012.

R
Mike

 

[VishMishra]

Then use the code provided by Alpha

Try something like this...

[COLOR=darkblue]Sub[/COLOR] Date_Increment_by_week()

    [COLOR=darkblue]Dim[/COLOR] dStartDate [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR], dEnd[COLOR=darkblue]Date[/COLOR] [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] dStartMonth [COLOR=darkblue]As[/COLOR] Date, dBeginingOfMonth [COLOR=darkblue]As[/COLOR] Date, dEndOfMonth [COLOR=darkblue]As[/COLOR] Date
    [COLOR=darkblue]Dim[/COLOR] Number_of_months [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], mo [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]

    dStartMonth = #11/1/2006#   [COLOR=green]'Day is assumed to always be the 1st of the month[/COLOR]
    Number_of_months = 2
    
    [COLOR=darkblue]For[/COLOR] mo = 1 [COLOR=darkblue]To[/COLOR] Number_of_months
    
        dBeginingOfMonth = DateAdd("m", mo - 1, dStartMonth)
        dEndOfMonth = DateAdd("m", mo, dStartMonth) - 1
        
        [COLOR=darkblue]For[/COLOR] dStartDate = dBeginingOfMonth [COLOR=darkblue]To[/COLOR] dEndOfMonth [COLOR=darkblue]Step[/COLOR] 7

            dEndDate = Application.Min(dStartDate + 6, dEndOfMonth)
            
            [COLOR=green]'Display dates for testing[/COLOR]
            MsgBox "Start date: " & dStartDate & vbCr & _
                   "End Date:  " & d[COLOR=darkblue]End[/COLOR]Date, , "Date Increment by Week"
                   
        [COLOR=darkblue]Next[/COLOR] dStartDate
        
    [COLOR=darkblue]Next[/COLOR] mo
    
End [COLOR=darkblue]Sub[/COLOR]

 

[CMIIandExcel]

Alpha

Thanks very much, I was going a slightly different way but yours makes more sense. Sorry i didnt reply sooner I missed your post (like i said Man Flu)

The Min function is the key

Thanks again, and thanks Vish for the effort, and for pointing my eyes down to Alphas posting

Regards

Mike