How do I determine "last week" period

[jonathanchye]

I am just wondering if there is a code to generate a date range of last week.
For example if I run the code yesterday (5/5) it will return the date from : 25/5 and to : 29/5.

Ideally by running this code it will return 2 values to 2 designated textboxes.

Thank you.

 

[gemma-the-husky]

why does 5th May produce 25th May to 29th May?

what denotes the start finish dates that you are interested in?

 

[jonathanchye]

why does 5th May produce 25th May to 29th May?

what denotes the start finish dates that you are interested in?

Hi,

Sorry to confuse you with the 5th May date. Basically I want to generate a report based on date range of "last week". To be more precise it's last working week. So basically Monday to Friday unless it's a holiday. I have a table which records all holidays which is used by another code to calculate NetWorkDays. I want to be able to incorporate the abilities to skip non-workdays as well with this code.

Right now what I do is take Friday's date and then subtract the appropriate numbers to get last week's range. This works if user runs report on a Friday.

However, this won't work if the user runs my code on any other days...

I've found this thread : http://www.access-programmers.co.uk/forums/showthread.php?t=64151

which uses DateAdd method but I think I need to modify the code a little so it shows full date? I am using the short date format at the moment.

 

[jonathanchye]

Well I've found a solution although not a very elegant one I've posted code here in case someone wants to do this. The code can be modified to show past 2 weeks, next 2 weeks etc as well easily.

' For this code you put in a date in Text37
' It will output first week day last week in Text 32 and last work day last week in Text 35

Dim firstDayofWeek As Date
Dim lastDayofWeek As Date
Dim temp2 As Date

temp2 = Me.Text37.Value ' This is just my test textbox to test few dates

' Check for first day of week counting holidays

firstDayofWeek = DateAdd("ww", -1, temp2 - Weekday(temp2) + 1)

Do While Weekday(firstDayofWeek, vbMonday) > 6
firstDayofWeek = firstDayofWeek + 1
Loop
' Check holiday table
Do While IsNull(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & Format(firstDayofWeek, "mm\/dd\/yyyy") & "#")) = False
firstDayofWeek = firstDayofWeek + 1
Loop

Me.Text32.Value = firstDayofWeek


' Check for last day of week counting holidays

lastDayofWeek = DateAdd("ww", -1, temp2 - Weekday(temp2) + 6)

Do While Weekday(lastDayofWeek, vbMonday) > 6
lastDayofWeek = lastDayofWeek + 1
Loop
' Check holiday table
Do While IsNull(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate] = #" & Format(lastDayofWeek, "mm\/dd\/yyyy") & "#")) = False
lastDayofWeek = lastDayofWeek + 1
Loop

Me.Text35.Value = lastDayofWeek