Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Finding Day of the Week in query (2002)

    Hi,

    I have a query that contains [care_date]. This represents the day of the week a visit is completed. The resulting paperwork is due into the office 1 day later. I was successful in obtaining that date by creating a colunm [datedue[care_date]+1)]. I then added a field that changed the [datedue] to the day name called [dayduename] and referenced [datedue] and set the format as ddd. So far everything is working fine. I now need to project out for weekends. I tried using an iif statement but ended up coming up with the same data as [care_date]. What I need to do is figure for the weekends. If the record is completed on a Friday, Saturday or Sunday, it isn't due into the office until Monday. I'm not sure of to get that result.

    Thanks,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Finding Day of the Week in query (2002)

    Try using this to get the monday date if the care_date is a friday,saturday or a sunday.

    ii(WeekDay(care_date)>5, care_date+9-WeekDay(care_date), care_date+1)

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Day of the Week in query (2002)

    Wow Pat that worked great! I think I acutally understood the code! I'm assuming that Access assigns Monday as day "1", Tuesday as Day "2" etc. thus why the formula works.

    Thanks!

    Leesha

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Finding Day of the Week in query (2002)

    An optional argument for the Weekday function is what day you want the first day of the week to be, but the default is Sunday.

    So sunday is day 1, Thursday is day 5.

    So iif(WeekDay(care_date)>5 is testing if it is greater than Thursday ie Friday or Saturday.

    By Sunday it is back to 1 again, the next day rule works OK
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding Day of the Week in query (2002)

    Thanks!!

Posting Permissions

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