Week Ending Date

CHI Brian · #1 (**permalink**) 02-10-12, 11:49

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.

pbaldy · #2 (**permalink**) 02-10-12, 12:25

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.

CHI Brian · #3 (**permalink**) 02-10-12, 12:33

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.

pbaldy · #4 (**permalink**) 02-10-12, 12:38

You don't want the "?" in there. I was demonstrating it from the Immediate window to show it worked.

CHI Brian · #5 (**permalink**) 02-10-12, 12:40

THank You Thank YOU!!!!! That is Fantastic!!!!

Sinndho · #6 (**permalink**) 02-10-12, 12:43

Quote:

Originally Posted by CHI Brian

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

pbaldy · #7 (**permalink**) 02-10-12, 12:45

Happy to help!

Internet Services

Web Development

Digital Marketing

Consumer Tech