Date Comparison - VBA nightmare!

[koptastic69]

I have a table that contains a field (called "At") of type Date/Time in the format yyyy-mm-dd.

I have another table that contains a field (called "EndDate") of exactly the same type and format. This tables data is a week ending/week number mapping table.

I've written a simple VBA module to iterate through the first table, matching the date with the date in the second table to find the relevant week number.

Here's my code;

Public Function SetWeekValues()
Dim inRST1 As Recordset
Dim sheet2 As Recordset

Set inRST1 = CurrentDb.OpenRecordset("dbo_TEPE", dbOpenDynaset)
Set sheet2 = CurrentDb.OpenRecordset("dbo_Sheet2", dbOpenDynaset)
inRST1.MoveFirst

Do Until inRST1.EOF

inRST1.Edit
sheet2.FindFirst "EndDate >=#" & inRST1!At & "#"
inRST1!WeekNo = sheet2!WeekNo
inRST1.Update
inRST1.MoveNext

Loop

inRST1.Close
sheet2.Close
End Function

An example of the output I am getting is that for a date of 2010-09-06, it is returning week 10. This should be 22. Curiously, 2010-06-09 IS week 10!

 

[Dairy Farmer]

I assume that your year starts on 1st April otherwise I would have suggested DatePart and done away with all the code.

You can still use DatePart though.

This is not actual code but a suggestion.
All assume week starts on Sunday and 1 Jan
DP1 = DatePart ("ww", year/04/01) = Weeks from 1st Jan to 1 April
DP2 = DatePart ("ww", date) = Weeks from 1st Jan to today
DP3 = DatePart ("ww",year/12/31) = Weeks in the year

If date<= year/12/31 then DP2 - DP1
If date>= year/01/01 then DP3 - DP1 + DP2