Query Previous Week Records

[Scott]

What expression will query all the records in the previous week?

 

[Michel Walsh]

BETWEEN Date()-DatePart("w", date()) -6 AND Date()-DatePart("w", date())



That assumes:
- first day of the week if Sunday
- the field you use to compare does NOT have a time value (other that
00:00:00 )



Note that Date() - DatePart("w", date()) returns you the previous
Saturday (if vbSunday is the first day of the week), so, removing 6 days to
it returns the Sunday of the same week.



Note that the week number brings problem near the year change, since week 1
may complete or may succeed to week 52 or 53 or 54 of previous year, so that
is why I didn't use a approach based on it.




Vanderghast, Access MVP

 

[Scott]

Worked great, thank you!

BETWEEN Date()-DatePart("w", date()) -6 AND Date()-DatePart("w", date())



That assumes:
- first day of the week if Sunday
- the field you use to compare does NOT have a time value (other that
00:00:00 )



Note that Date() - DatePart("w", date()) returns you the previous
Saturday (if vbSunday is the first day of the week), so, removing 6 days to
it returns the Sunday of the same week.



Note that the week number brings problem near the year change, since week 1
may complete or may succeed to week 52 or 53 or 54 of previous year, so that
is why I didn't use a approach based on it.




Vanderghast, Access MVP