dynamic weeks

[Guest]

In a query I need to extract the current week plus last week. Data is loaded
daily so the current week might be 1 day or 5 days. Last week will always be
5 days.

Since this is M-F only, I've included a time of week (weekday or weekend)
identifier.
I have been able to get last week and I can get this week independently. I
just can't get both to show up at the same time. I don't want to manually
have to alter the query weekly.

I can't say today minus x number of days since the I exclude weekends and
the number of days in the current week vary depending on what day we are on.
I have also created a unique week number for each week.

What do I need to do to automate this process?
thanks in advance.

 

[John Spencer]

CurrentWeek
Between DateAdd("d",2-WeekDay(Date()),Date()) and Date()

Last Week
Between DateAdd("d",-5-Weekday(Date()),Date()) and
DateAdd("d",-1-Weekday(Date()),Date())

Field: YourDateField
Criteria(Line 1): Between DateAdd("d",2-WeekDay(Date()),Date()) and Date()
Criteria(Line 2): Between DateAdd("d",-5-Weekday(Date()),Date()) and
DateAdd("d",-1-Weekday(Date()),Date())

Or using two fields - one to get the date range and the second to include
days M to F.
Field: YourDateField
Criteria(Line 1): Between DateAdd("d",-5-Weekday(Date()),Date()) and Date()

Field: DayOfWeek: WeekDay(YourDateField)
Criteria: Between 2 and 5

 

[Michel Walsh]

Hi,

In addition to John's suggestions, you can maybe try a

WHERE DateDiff("ww", dateTimeField, now() ) IN (0, 1)



Hoping it may help,
Vanderghast, Access MVP