Discussion Locked

Date conversion problem

0 helpful
8 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

From within Access 97 (ideally in a query) I would like to convert a date in the format of year, week number and day number back into calendar date format.

e.g. Today is week 23, year 2001 day 4 which is 7/06/2001 (7th June 2001).

If it can't be done in a query a VBA solution would be fine.

Thanks,

Ian

8 replies

by mark.thomson

Here is a sample query that does the conversion

SELECT calendartest.Dayno, calendartest.weekno, calendartest.year, [dayno]-1+(([weekno]-1)*7)+DateValue("01/01/" & [year]) AS fulldate
FROM calendartest;

This is just off the top of my head, there may be a more elegant solution

by ian k

Thanks for this Mark. Unfortunately I can't get exact results with the above. However I've learnt something new from you.

Cheers,

Ian

by rfoster

DateAdd may also be appropriate. For example:

CalendarDate = DateAdd("w", DayNumber, DateAdd("ww", WeekNumber, DateSerial(2001,1,1)))

(Note, this is from memory and may be inaccurate, also I rarely deal with dates in the form you mention - test well!)

Regards,
Richard

by ian k

Hi Richard,

Thanks for the help. Unfortunately this doesn't give exact result. I developed your answer into:

DateAdd("w",[DayNum]-1,DateAdd("ww",[Week No]-1,DateAdd("yyyy",[Year Number]-1996,"1-Jan-96"))

My dates don't got back past 96 hence me subrtracting and adding from 1-1-96. However It doesn't give the same answer as me looking up in my diary.

e.g. day 7, week 25, year 1998 should return 28-6-98, however the above returns 24-6-98. Close but not exact !!

by mark.thomson

It looks like Day 1 week 1 for any year is the first monday in January ( which happens to be Jan 01 for 2001) thus your solution has to take account of which day of the week O1 jan [year number] falls on

so try SELECT calendartest.Dayno, calendartest.weekno, calendartest.year, [dayno]-1+(([weekno]-1)*7)+DateValue("01/01/" & [year]) + weekday(DateValue("01/01/" & [year])) - 2 AS fulldate
FROM calendartest;

(The -2 is to take account of the fact that the weekday function returns 2 for monday)

by ian k

This still isn't exact enough, I'm afraid. Thanks for your effort though.

This discussion is closed to new replies.