Date Problem Rolling week number at change of year 2

[act2]

My database needs to keep track of week numbers and at the change of the year start at wk 1 again. Some of the jobs tracked can run for several weeks. So data can look like this.

WK 51 06
WK 52 06
WK 53 06
WK 01 07
WK 02 07

I used DatePart() to extract the weeks and year but for the life of I can not figure the formula that will work and I'm sure it's pretty simple. Any help will be appreciated. Thanks

Option Compare Database
Option Explicit
Private Sub cmdEnter_Click()

Dim wtb As Integer
Dim x As Integer
Dim Y As Integer
Dim w As Integer
Dim dh As Integer
Dim lohr As Integer
Dim CNCphr As Integer
Dim Layout As String
Dim Design As String
Dim CNCProg As String
Dim YR As Integer



wtb = DateDiff("ww", Me.StartDate, Me.CompleteDate)
Me.WeeksToBuild = wtb
x = DatePart("ww", Me.CompleteDate)
w = DatePart("ww", Me.StartDate)
dh = Me.txtDesign / wtb
lohr = Me.txtLayout / wtb
CNCphr = Me.txtCNCProg / wtb
Y = wtb + w
YR = DatePart("YYYY", Me.StartDate)
  


If Me.ck1.Value Then
Design = Me.Designlbl.Caption
End If


         
            For x = w To Y
                  If x < Y Then
                  If Design = "Design" Then
                        Forms.frmMain.frmsubMain.Form.WkNumber = x
                        Forms.frmMain.frmsubMain.Form.JobType = Design
                        Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
                        Forms.frmMain.frmsubMain.Form.Year = Me.Year
                        Forms.frmMain.frmsubMain.Form.JobHr = dh
                        End If
                             Me.Refresh
                             Forms.frmMain.frmsubMain.Form.Recordset.AddNew
                            End If
                            Next x

                      End Sub

 

[PHV]

A starting point:
For myDate = Me!StartDate To Me!CompleteDate Step 7
Debug.Print "WK " & Format(myDate, "ww yy")
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[act2]

PHV, You'll have to excuse my ignorance but I'm not sure how to get it in to my code correctly. I may have misled you with my original question showing my data. What I have is a sub form with a field for weeks and one for years. I tried putting your code in and looking at it in the debug window. But it would repeat 4 or 5 times. Loop problem.
Thanks for your time.

 

[CautionMP]

Building on the direction PHV was going, try something like this:

[green]'...[/green]
[b]Dim MyDate As Date[/b]
[green]'...

'...[/green]
'[red][s]For X = w To Y[/s][/red]
[b]For MyDate = Me.StartDate To Me.CompleteDate Step 7[/b]
  '[red][s]If X < Y Then[/s][/red]
    If Design = "Design" Then
      '[red][s]Forms.frmMain.frmsubMain.Form.WkNumber = X[/s][/red]
      [b]Forms.frmMain.frmsubMain.Form.WkNumber = DatePart("ww", MyDate)[/b]
      [green]'Catch the 53rd week here and adjust[/green]
      Forms.frmMain.frmsubMain.Form.JobType = Design
      Forms.frmMain.frmsubMain.Form.ToolNum = Me.ToolNum
      '[red][s]Forms.frmMain.frmsubMain.Form.Year = Me.Year[/s][/red]
      [b]Forms.frmMain.frmsubMain.Form.Year = Year(MyDate)[/b]
      Forms.frmMain.frmsubMain.Form.JobHr = dh
    '[red][s]End If[/s][/red]
    Me.Refresh
    Forms.frmMain.frmsubMain.Form.Recordset.AddNew
  End If
'[red][s]Next X[/s][/red]
Next MyDate
[green]'...[/green]

The long and short of it, when doing week calculations using [tt]DatePart()[/tt] and [tt]DateDiff()[/tt] the weeks are caculated by counting the number of times a particular day (Sunday, vbSunday, by default) happens between two dates.
Since there are 52 weeks and 1 day in a normal year this can cause a 53rd week to show up using these calculations. By stepping through the dates seven days at a time ([tt]Step 7[/tt]) you won't be able to eliminate this but you can catch the 53rd week and re-write the value.

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[act2]

CautionMP and PHV, Thank you for helping me with this, It solved all my problems. Stars for both.