different date criteria for days of week

[Amanda2]

I need to get records based on a range of dates that change depending on when I am running the query.

ie: If I run it on Monday I need records with dates from between 10 and 16 days ago; if I run it on Tuesday, I need records with dates from between 11 and 17 days ago...

DateAdd does not seem to work with "Between x And y".

Any suggestions? I'm pulling hair out.

 

[Trendsetter]

Sounds to me as though you are wanting to specify a specific week and ignore the individual days of that week.
That way it doesn't matter when you run the query!

Look up formatting dates in the Help!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[PHV]

And what about this ?
WHERE [date field] BETWEEN (Date()-15-Int(Format(Date()+3,"w",2))) AND (Date()-9-Int(Format(Date()+3,"w",2)))
Or this ?
WHERE (Date()-[date field]-Int(Format(Date()+3,"w",2))) BETWEEN 9 AND 15

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Amanda2]

Thanks Frank, you're right! Now I just have to figure out the week functions.

DateAdd("w",-2,[DateField]) ???

 

[PHV]

Something like this ?
WHERE Format([DateField],"ww")=Format(DateAdd("ww",-2,Date),"ww")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244