Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date set back to Sunday (2003)

    What would be the best way to convert all days in a week back to the Previous Sunday?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date set back to Sunday (2003)

    Hi Benji

    How about this function in a query:

    IIf(Weekday([date])>1,[date]-(Weekday([date])-1),[date])

    Where [date] is the date field you need
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date set back to Sunday (2003)

    Thanks it works. Is there anyway you can truncate dates in access?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date set back to Sunday (2003)

    Sorry Benji I popped out for my evening run and have just picked this up. My understanding of truncate is braek a date to dd, mm, yy , hh formats or day , month, year, hours, minutes and seconds...I hope this is what you mean. If so you can use the datediff function.

    Have a look here for the breakdown

    http://www.databasedev.co.uk/ms_acce...functions.html
    Jerry

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

    Re: Date set back to Sunday (2003)

    Slightly simpler:

    [DateField]-Weekday([DateField])+1

    where DateField is the name of the relevant field.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date set back to Sunday (2003)

    Thanks for the tip 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
  •