CODEWITHSUNDEEP
ms-accessdate
Marko
Marko

Reputation: 65

Ms Access to show only previous week's records

In MS Access I am trying to filter to show only records from previous week. I have colums showing weeknumber based by record date. Using DatePart("ww",Date(),2,2)-1 in criteria field works well if current week is not 1. It does not work to previous year.

How I can show only records from previous week or 2 weeks, even if it goes to previous year?

Upvotes: 1

Views: 25186

Answers (2)

HansUp
HansUp

Reputation: 97101

For the most recent week of data, start with something like this ...

YourDateField Between (Date() -7) AND Date()

That approach will not break when your target YourDateField range include dates from 2 years. Another advantage of that approach is the query can make use of an index on YourDateField, if you have one. In that case the db engine can examine the index to find which rows match your criterion and read only the matching rows instead of all rows in the record source. (If you don't have an index on YourDateField, add one to see if it speeds up the query significantly. You can drop the index if it's ineffective.)

Conversely using a WHERE condition based on the DatePart() function will force the db engine to examine each and every row in the query's record source. That can be a performance bottleneck which can be significant with a huge table, especially if it is a linked table which requires data be read across a network connection.

Edit: I misunderstood what you wanted. For the the week which started on the previous Monday, I used this condition:

Between Date() - ( 6 + Weekday(Date(), 2))
    AND Date() - Weekday(Date(), 2)

The db engine will evaluate those expressions one time only when running the query, so while this approach is more complicated than what I first suggested, it can still offer the potential performance improvements over using DatePart() for every row in the data source.

Upvotes: 4

Marko
Marko

Reputation: 65

HansUp gave me an idea to solve my problem. Using Between (Date()-7-Weekday(Date(),2)+1) And (Date()-Weekday(Date(),2)+1), should give me previous calendar week. Weekday() returns daynumber of the week. Substracting weekday number (+1) from date should return Monday of the week.

Upvotes: 0

Related Questions