Get next week number when year end

[scriggs]

I have a table which shows holds data stored with week no and year, i.e.

Week Year
3 2007
2 2007
1 2007
52 2006
51 2006
50 2006
49 2006
..
3 2006
2 2006
1 2006
53 2005
52 2005
51 2005
50 2005


I need to select a week and then pick the next 6 weeks, i.e. pick 50,2006 - then 50,51,52,1,2,3 but 50,2007 then 50,51,52,53,1,2

How can I get Access to calc the next week around a year end?

 

[PWise]

Select Top 6 Week,year
From TableName
Where year*100 + week > Selectedyear*100 + Selectedweek
order by year, week

 

[scriggs]

Not quite what I was looking for, but a good lateral thinking move. I am going to use this for now, only problem is if the data in the my source is missing a week of data.

Thanks.

 

[LARiot]

Have you tried the DateAdd() function?

 

[PWise]

LaRiot:
How will you do it with the DateAdd() Function?

 

[LARiot]

I guess it would be more effort to convert it to a different date format, add six weeks with DateAdd() then convert it back. An easier method might be:

Week = Week + 6
If Week > 52 Then
Week = Week - 52
Year = Year - 1
End If

IncrementWeek(52, 2007, 6)

Function IncrementWeek(Week As Integer, Year As Integer, Increment As Integer) As String

  Week = Week + Increment
  If Week > 52 Then
    Week = Week - 52
    Year = Year - 1
  End If

  IncrementWeek = Week & " " & Year
End Function

 

[PHV]

I think you meant this:
Year = Year [!]+[/!] 1

 

[LARiot]

No, no this is time machine code. It takes the user back in time.