Calculating a week ending date

[Jon]

Hi,

I have a db table with a field containing a date for when some overtime was
worked and another field which I wish to populate with a "Week Ending" date
based on the date of the overtime. Our weeks actually end on a Wednesday. I
would be grateful for assistance with a query to do this.

The table looks like this:

Week Ending | Date of Overtime | Name



Thanks in advance

Jon

 

[Scott Lichtenberg]

Jon

Take a look at the Weekday Function. If you week ends on a Wednesday, it
begins on a Thursday.

Weekday(#11/14/08#, vbThursday) = 2

I'm assuming that you are looking for the Wednesday after the overtime date.
You would the difference between 7 and the value returned by your weekday
function to get the next Wednesday.

DateAdd("d", 7 - Weekday(dtDate, vbThursday), dtDate)

Copy the following into the immediate window to test:

DateAdd("d", 7 - Weekday(#11/10/08#, vbThursday), #11/10/08#)

It returns 11/12/2008 which is a Wednesday. 11/14 returns 11/19.

Hope this helps.
Scott

 

[KARL DEWEY]

This will calculate the Week Ending date. Substitute your field name for
[Date Open] --
IIf(Format([Date open],"w")<=3,DateAdd("d",4-Format([Date open],"w"),[Date
Open]),IIf(Format([Date open],"w")=4,[Date Open],DateAdd("d",Format([Date
open],"w")+(Format([Date open],"w")-4),[Date Open])))

 

[Klatuu]

This will do it. I used data for testing. Just replace Date with your
actual field or control name.

iif(dateadd("d", vbwednesday-datepart("w",date),date)<=date,dateadd("d",
7+vbwednesday-datepart("w",date),date),dateadd("d",
vbwednesday-datepart("w",date),date))

 

[John Spencer]

One minor change if you are going to use this is a query is to replace
vbThursday with the value of the visual basic constant.

DateAdd("d", 7 - Weekday([Date of Overtime], 5), [Date of Overtime])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jon]

This worked great. My thanks to all who responded.

Jon

 

[Jon]

This worked great. My thanks to all who responded.
Jon