Help with criteria requirements

[JINGRAM]

Hi -
I have made a query of specific info that I need to pull a report from - the only problem is that I am not an access expert - especially when it comes to criteria or sql - what I have in my qry is a field that has a week span and a field that has a week # (example = week 19 is 5/6/02 - 5/12/02) what I need to do is create another field in my qry (projected date) and somehow ???? have the criteria look at the first date on the week span (5/6/02) and subtract a week or maybe even a week & 1/2 from that date (4/29/02 for example) is there a way of doing that or maybe doing something close to it?
Here is a little background on what I am trying to achieve - I have a database that has info in it about some parts we are having shipped to us - the week span & week groups those parts together by once again the week & week span - i need to be able to them that we need the parts exactly say 7-12 days prior to that week?
I didn't know if there was an easier way to somehow group the dates together other than by using a week format? maybe it would be easier if I just use the date that i have and just say give me a date 7 days before this date - how would I do that as well in a qry - making a new field -
thank you for any assistance!!!
Jingram

 

[dalchri]

To do date math, use the DateAdd function. For example, 7 days before 5/6/02 would be written:
[tt]
DateAdd("d",-7,#5/6/02#)
[/tt]
or in general for a field in a query:
[tt]
SELECT DateAdd("d",-7,YourDateField) AS SevenDaysPrior
FROM YourTable
[/tt]

 

[JINGRAM]

Okay - so I figured that part out
In my new field I wrote as - projected date:=DateAdd("w",-7,[BDT])
It returned exaclty what I wanted - ( My BDT field has different dates in it)
but........can I also ask it -7 first day of the week? See it returns Sat and Sun dates and I just need the Mon dates? did I say that right? sorry-
Let me try again

Say My BDT field has 5/02/02,5/03/02 - can I have it return -7days but the date only be a monday?

Thanks again
JINGRAM