To get the same date for the previous 12 months, you need to put criteria on
two fields.
First Field:
Field - DateNeeded:(([IT].[Date]>=DateSerial(Year([IT].[Date]),
Month([IT].[Date]), 6-5)) And ([IT].[Date]<=DateSerial(Year([IT].[Date]),
Month([IT].[Date]), 6+5)))
Criteria - True
This will return True or False if IT.Date is 6 +/- 5 days. You will have to
verify that this is a valid day for all months (i.e. <=28) or you won't get
the results you expect. This will error out if any of the records contain a
Null value in IT.Date.
Second Field:
Field - [IT].[Date]
Criteria - >= DateAdd("m", -12, [IT].[Date])
This will give you 7/21/2003 or later if today is 7/21/2004. If you want to
include all of July 2003 then the Second Field should be:
Field - MonthsBack: Format([IT].[Date], "yyyymm")
Criteria - >= Format(DateAdd("m", -12, [IT].[Date]), "yyyymm")
This will use just the year and month values. So, today would show up a
200407 and 12 months ago would show up as 200307.
Just a quick comment, you shouldn't use a reserved word, such as Date, as
the name of a field, variable, or other items. This will prevent Access from
confusing it with the Date function in VBA.
--
Wayne Morgan
Microsoft Access MVP
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Wayne,
it.date is a date field. Typo error
Yes, looking for extraction of same date
for previous consecutive month.
There after, have to do a subtraction i.e.
Date qty Diff (i.e. 5 - 4 = +1 also looking for this value to
display)
5/1/2004 5 +1
4/1/2004 4
Captain
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message news:<Q2***************@newssvr22.news.prodigy.com >... What data type is IT.Date? "IT.date='01DEC2003'd-30" is not a standard
date/time field type. Is the value actually a date and you are attempting
to
subtract 30 from the date of 01 DEC 2003? It appears that what you want
is
the value on the first day of the month (+/- 5 days) for the previous 12
months. Is this correct?
--
Wayne Morgan
MS Access MVP
"captain" <ca**********@yahoo.com> wrote in message
news:27**************************@posting.google.c om... > Below is the sql for data with same date need
> to extract + or - 5 days data of same date also.
>
> How to also get data of + and - days related to same date.
>
>
> SELECT IM.Area,
> IM.Location,
> IT.itemid,
> IT.date,
> IT.Qty,
> IT.Rate,
> IT.Amount
> FROM ITran as IT
> INNER JOIN
> IMast as IM
> ON IT.itemid = IM.itemid
> WHERE IT.itemid="A101"
> and (IT.date='01DEC2003'd
> or IT.date='01DEC2003'd-30
> or IT.date='01DEC2003'd-61
> or IT.date='01DEC2003'd-91
> or IT.date='01DEC2003'd-122
> or IT.date='01DEC2003'd-153
> or IT.date='01DEC2003'd-183
> or IT.date='01DEC2003'd-214
> or IT.date='01DEC2003'd-244
> or IT.date='01DEC2003'd-275
> or IT.date='01DEC2003'd-303
> or IT.date='01DEC2003'd-334
> or IT.date='01DEC2003'd-365)
> order by IM.Area, IT.itemid, IT.date desc;
> quit;
>
> Captain
