Marking Specific Date based on Day of the week (Access 2003 SP2)

Topic

New Reply

I need to Update a Field with the Date of the LAST Friday, unless it is FRIDAY, then I would like the Current Date:

I will create a query or macro that can be run any day of the week based on a Select Query. The Query will select a a dataset, Determine todays date, and Update the field Weekof: to Last Fridays Date.
If today happens to be a friday, then update the field Weekof: to current date.

Update Field: Weekof:

If today is Saturday thru Thursday of the following week, Add Last Friday’s Date
If Today is FRIDAY, put todays Date.

Today is Wednesday June 11, 2008

The field will be udated as follows:
Wed = 06/06/2008
Thur= 06/06/2008
Fri = 06/13/2008

Sat = 06/13/2008
Sun = 06/13/2008
Mon = 06/13/2008
Tues = 06/13/2008
Wed = 06/13/2008
Thu = 06/13/2008
Fri = 06/20/2008

Reply | Quote

Replies

You can use the following expression to return “last Friday”:

Date()-(Weekday(Date(),7) Mod 7)

The Weekday function returns a number for the day of the week in the range 1, …, 7.
If the second argument is 7, Saturday = 1, Sunday = 2, …, Thursday = 6, Friday = 7.
The Mod operator returns the remainder of this number after division by 7, so Saturday = 1, Sunday = 2, …, Thursday = 6, Friday = 0.
This number of days is subtracted from the date itself. You’ll always end up at “last Friday”.

Reply | Quote

Excellent work….

I was able to make the updates and it tested great!!!!

Thanks Hans

Reply | Quote