Calculated Field for Week Ending

[Guest]

I have dates in my data which are a text field formatted as "yyyymmdd".
So 2nd March 2006 appears as 20060302.

I would like to add a calculated field that will convert my date data into a
week ending Friday value. E.g in the case of 20060302 this is a Thursday so
the result would be 20060303. Either yyyymmdd or mmddyyyy format is ok.

Any help appreciated.

Bruce

 

[Jeff Boyce]

Bruce

Working with a text string like "yyyymmdd" in Access is ... work. Working
with a date/time field in Access (for example, to calculate an end-of-week
date) is fairly easy (there are many date/time functions available).

Do you have the option of converting the text string to an actual Date/Time
value? You could use the Left(), Mid(), Right() functions and the
DateSerial() function to get a date, before applying further functions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

How about
DateValue(Format([TheDateField],"@@@@-@@-@@"))
To convert the date

Then using that calculation to get Friday as the last day of the week would
be

DateAdd("d",7-WeekDay(DateValue(Format[TheDateField],"@@@@-@@-@@")),7),DateValue(Format[TheDateField],"@@@@-@@-@@")))

As Jeff observed, this gets complicated.