Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am looking for a way useing either a function or query expresion to find the date of the next Sunday after a given date.

    Example: the Invetory Date = 5/13/2010, I would like to have Access return the date 5/15/2010.

    Is this possible?


    Thanks
    Richard

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try this is a query

    NextSunday: DateAdd("d",7-Weekday([Inventory Date],2),[Inventory Date])

    Weekday([Inventory Date],2) tells you the day of the week, for the provided date., using Monday as Day 1.
    Subtract that from 7 to tell how many days until the next Sunday.

    PS: I have just been testing a few slight variations of this, and this seems to me to be returning the correct answers.

    If the Inventory Date is a Sunday, do you want it to return the same date, or the following Sunday? This expression returns the same date.
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This should do the trick. The inventory date should never be a Sunday.


    Thank you for the help.
    Richard

Posting Permissions

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