Week number problem

[Mike Collard]

I have a table that holds average values by week number -
the last week held is currently week 21 so I now need to
add data for week 22 and week 23.

If the table held data by date I could use the last date
as the starting point for appending the latest week's data
but how can I use the last week number to return the
appropriate starting date bearing in mind that the source
of the append query is an Oracle table with an ODBC link
with the data stored by date?

It's easy to return a week number from a date but how can
I return the date that corresponds to the first day of
week 22 when all I have is the week and year i.e. Week 22
Year 2004?

Thanks

Mike Collard

 

[John Spencer (MVP)]

DateAdd("ww",22-1,DateSerial(2004,1,1)) will add 21 weeks onto the first day of
the specified year.

You can adjust that using DateAdd a second time. X = above formula.

DateAdd("d",1 - Weekday(X),X) (Sunday of the week containing X)

So putting that all together, you end up with
DateAdd("d",1 - Weekday(DateAdd("ww",22-1,DateSerial(2004,1,1))),DateAdd("ww",22-1,DateSerial(2004,1,1)))

I have no idea if this will work with ORACLE tables.

 

[Mike Collard]

Hi John

Thank you - I think your solution is spot on. Will try it
out tomorrow.

Mike Collard