Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    You can use the following expression to return "last Friday":
    <code>
    Date()-(Weekday(Date(),7) Mod 7)
    </code>
    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".

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Marking Specific Date based on Day of the week

    Excellent work....

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

    Thanks Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •