Creating Date Range by Month, Year & Week

[r0nniem]

In a form, I've the following fields:

Month, Year, BegTransDate, EndTransDate

When Month and Year are updated, it fills in the BegTransDate and EndTransDate.

Month = August
Year = 2006
BegTransDate = 01/08/06
EndTransDate = 31/08/06

I've added a "Week" combo box, listing week 1, week 2 etc and which is the criteria for the query that calcuates the week number. My problem is that I want it to always show Monday to Sunday (eg. Aug 06 Week 1 only shows Tuesday 01/08/06 to Sunday 06/08/06).

Therefore, I was wanting to have the "Week" combo:
1. List the correct number of weeks for the month
2. On Update, update BegTransDate and EndTransDate in the following date range:

August 2006 - Week 1 = 31/07/06 to 06/08/06 etc

August 2006 - Week 4 = 28/08/06 to 03/09/06

September 2006 - Week 1 = 04/09/06 to 10/09/06

Can anyone help me out?

thanks
Ronnie

 

[CautionMP]

r0nniem,
Here is a starting point, you could use the function to supply the RowSource (value list) to your combo box.

[navy]Function[/navy] FillCombo(BegDate [navy]As[/navy] Date, EndDate [navy]As[/navy] Date) [navy]As String[/navy]
[navy]Dim[/navy] dteCounter [navy]As Date[/navy]
[navy]Dim[/navy] intWeek [navy]As Integer[/navy]
[green]'Adjust start date to be a Monday[/green]
dteCounter = BegDate - Weekday(BegDate, vbTuesday)
[navy]Do[/navy]
  [navy]If[/navy] Day(dteCounter) < 7 [navy]Then[/navy]
    [green]'This is the first full week of the month[/green]
    intWeek = 1
  [navy]Else[/navy]
    [green]'this is a subsequent week[/green]
    intWeek = intWeek + 1
  [navy]End If[/navy]
  [green]'Output the results[/green]
  FillCombo = FillCombo & "Week " & intWeek & ";" & dteCounter & " to " & dteCounter + 6 & ";"
  dteCounter = dteCounter + 7
[navy]Loop[/navy] Until dteCounter >= End[navy]Date[/navy]
[navy]End Function[/navy]

[tt]Me.Combo1.Rowsource = FillCombo(#08/01/2006#,#09/30/2006#)[/tt] gave me the following result in a two column combo box:
[tt]Week 1 7/31/2006 to 8/6/2006
Week 2 8/7/2006 to 8/13/2006
Week 3 8/14/2006 to 8/20/2006
Week 4 8/21/2006 to 8/27/2006
Week 5 8/28/2006 to 9/3/2006
Week 1 9/4/2006 to 9/10/2006
Week 2 9/11/2006 to 9/17/2006
Week 3 9/18/2006 to 9/24/2006
Week 4 9/25/2006 to 10/1/2006[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)