Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Week Ending Date

    Good Morning,

    I tried searching for this in the forum but couldn't find anything that was similar to what I was looking to do.

    I work in a Healthcare environment and need to comply some statistics based on Service Date Field in my table.

    Basically if a patient comes in and is seen on 1/3/12 and again on 1/18/12, the weekending date for each should be 1/7/12 and 1/21/12 respectivly.

    How do I get Access to figure this out? Please keep this as easy as possible, I am not good with SQL, I use all the basic stuff in access at this point. SQL confuses me usually, specially writing from scratch.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This type of thing:

    ?dateadd("d",7-weekday(#1/18/12#,0),#1/18/12#)
    1/21/2012

    using your field instead of the hard-coded date.
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    Paul, Thank you. I am trying to type this into my query but it keeps dissapearing on me. So I don't think I am typeing something right. This is what I am doing:

    calculated:?dateadd("d",7-weekday( [Week Ending]![DOS] ,0), [Week Ending]![DOS] )

    This isn't the actual file as I am still trying to obtain that, so I built a quick table with some dates in it to see if it worked.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You don't want the "?" in there. I was demonstrating it from the Immediate window to show it worked.
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    THank You Thank YOU!!!!! That is Fantastic!!!!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CHI Brian View Post
    Basically if a patient comes in and is seen on 1/3/12 and again on 1/18/12, the weekending date for each should be 1/7/12 and 1/21/12 respectivly.
    Formulated with other words, this consists in finding the next Saturday from a given date.

    This gives us a numeric value (between 1 and 7) corresponding to the day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday):
    Code:
    DatePart("w", SomeDate, vbSunday)
    The difference between a given date and the corresponding next Saturday is the day of the week as obtained hereabove minus 7, so:
    Code:
    Function NextSaturday(ByVal SomeDate As Date) As Date
    
        NextSaturday = DateAdd("d", 7 - DatePart("w", SomeDate, vbSunday), SomeDate)
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Posting Permissions

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