CODEWITHSUNDEEP
ms-access-2007
nixxrite
nixxrite

Reputation: 125

Access criteria to return data with date greater than previous Friday

I currently pull a date field into a query I'm using for access 2207 with the criteria of:

create_dt_tm >Now()-7 (which pulls everything created in the last 7 days)

Is it possible to make the criteria greater than the previous Friday? hence only pulling >Now()-3 if my query is run on Monday, -4 on a Tuesday etc?

Upvotes: 1

Views: 666

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123809

The Weekday() function returns

Sunday     1
Monday     2
Tuesday    3
Wednesday  4
Thursday   5
Friday     6
Saturday   7

and the offsets to the previous Friday are

Sunday     -2
Monday     -3
Tuesday    -4
Wednesday  -5
Thursday   -6
Friday     -7
Saturday   -1

so the expression

DateAdd("d", -1 - IIf(Weekday(Date) = 7, 0, Weekday(Date)), Date)

will return the date of the previous Friday.

Upvotes: 1

Related Questions