building a date up

[jon]

Hi all.

I have 2 field a "week" and "year" and I want to make a 3rd field which is
the date of the Monday(not really bothered what day though) of the other 2
fields.

Can some one please point me in the right direction.
Using access 97

Thanks

Jon

 

[Steve Schapel]

Jon,

Try it like this...

MondayDate: DateSerial([YearField],1,1) + 2 -
DatePart("w",DateSerial([YearField],1,1)) + ([WeekField]-1) * 7

 

[jon]

It works.
Now I have to understand it
Thanks very much

Jon

Jon,

Try it like this...

MondayDate: DateSerial([YearField],1,1) + 2 -
DatePart("w",DateSerial([YearField],1,1)) + ([WeekField]-1) * 7

--
Steve Schapel, Microsoft Access MVP

Hi all.

I have 2 field a "week" and "year" and I want to make a 3rd field which
is the date of the Monday(not really bothered what day though) of the
other 2 fields.

Can some one please point me in the right direction.
Using access 97

Thanks

Jon

 

[Steve Schapel]

Jon,

Translation ;-)...

Take the first day of the required year.
Subtract the weekday number (Sunday=1 to Saturday=7), to give the
previous Saturday.
Add 2 days, to give the Monday in Week 1.
Add the number of days (x weeks * 7) between Week 1 and Week x.
Therefore the date of the Monday in Week x.

Make sense?