Limiting Query to Pervious Week

[Guest]

Good afternoon,

Please would you let me know how I can limit a query using the date field to
include only those dates from the previous week?

Thanks very much

 

[Guest]

Use a column in your query like --
WeekNum: Format([YourDateField],"ww")

Set criteria for the column as --
Format(DateAdd("ww",-1,Date()),"ww")

 

[Wayne Morgan]

By previous week do you mean the previous 7 days or the previous calendar
week (Sun - Sat)?

1) Previous 7 days, use the criteria on the date field

=Date() -7 And <Date()

The SQL for the WHERE clause would be
WHERE ([TableName].[DateField]>=Date()-7 And [TableName].[DateField]<Date())


2) Previous calendar week, create a calculated field
DatePart("ww", [TableName].[DateField])

and set the criteria to
DatePart("ww", Date()) -1

The SQL for the WHERE clause would be
WHERE (DatePart("ww", [TableName].[DateField])=DatePart("ww",Date())-1)

 

[OfficeDev18 via AccessMonster.com]

Hi, Dan,

You can use the DateAdd() function, e.g. WHERE TheDateField Between (DateAdd
("ww",-1,Date()) - Day(Date())+1) And (Date() - Day(Date()))

Hope this helps,

Sam