How to filter records for the previous week??

[breixo]

I've created a query in Access that gives me all the records for the previous month for a given section of the business by filtering on the criteria'Month(Now()) - 1' for close_date. I need to do the same but weekly but 'WEEK' is not a reserved word like MONTH'.

Any idea?

Cheers

 

[CautionMP]

breixo,
[tt]Date()-7[/tt]?

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[breixo]

Thanks for that CautionMP but 'Date()-7' only gives us the records for today - 7. In any case what I'd like to do is run a query able to give me always the numbers for the previous week regardless of the day of the week that I run the query.

Any idea?

Cheers

 

[ThomasLafferty]

Hi there:
Try this:
Between Date() And Date() - 7

Live once die twice; live twice die once.

 

[SkipVought]

Hi,

Try playing with something like...
[tt]
INT((MyDate-2)/7)*7)+2
[/tt]
which calculates the MONDAY date for any date in the week.

Skip,
[sub]
[red][/red]
[/sub]

 

[CautionMP]

breixo,
I'm sorry I didn't pay very close attention to your original post. Skip probably has the best recomendation but for future reference you can use [tt]Format()[/tt] to get a week number if you use [tt]"ww"[/tt] for the format argument.
[tt]Format(Now(),"ww")-1[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[breixo]

Awesome!!

Thanks a mil guys!!