Date Problem (AXP SP-1)

Topic

New Reply

Does anyone know of a bit of code that would give me the beginning and ending dates of “last week”? I am trying to automate the printing of some labels than need to be printed weekly. I would like to click on a button that would determine the dates of “last week” and feed them to my query. I’m thinking this can be done using format(now(), “ww”) somehow, but I can’t figure it out. Anybody have any ideas?

Reply | Quote

Replies

Assume Sunday is the first day of the week, Monday is the second, etc.
intDayOfWeek is the day we are looking for. Sunday(first day of week) would be 1, Saturday(last day of week) would be 7.

To return the date of the first day of last week, try this:
dtmDate – Weekday(dtmDate) + intDayOfWeek -7
If today is 10/09/02, and we want to know the date of the first day of last week’.
#10/09/02# – Weekday(#10/09/02#) + 1 -7
Returns 9/29/02

To find the last day of last week, just change the 1 to 7
#10/09/02# – Weekday(#10/09/02#) + 7 -7
Returns 10/5/02

Later,
bruce

Reply | Quote

One minor point to add to Bruce’s excellent response – international date settings can create problems with this kind of logic, as Sunday may not be Day 1 in some locations. There is some additional info in the Help files and on the MS Knowledge Base about those issues.

Reply | Quote

You can do it like this:

Dim dtmWeekStart as Date
Dim dtmLastWeekStart as date
 
dtmWeekStart = DateAdd("d", -1 * Weekday(ThisDate), ThisDate)
dtmLastWeekStart = DateAdd("d",-7,dtmWeekStart)

To get the ending date of the week, use DateAdd to add 6 to the beginning date.

I know this has been posted in the Lounge by someone before, but I couldn’t find it so you’re getting my version.

BTW, this approach doesn’t care which day the week starts on or what the date format is.

Reply | Quote