calculate dates

[favor08]

have serval date fields in my table that i need to perform calcualations to bring bring a certain # of items.

Fields:

RptDte Date filed closed always the last date of previous month--- 6/30 for this example
EntryDte Date item was entered into system --could be multiple dates
EffDte Date item was or will be effective.--could be multiple dates


My first critera needs to be

Any items with an EfftDte that is 2 months or prior from RptDte (6/30/2007) that was Entered (EntryDte Field) in May or June (m-1 or m-2) from current month.

Second criteria would be

Any items with an effdate that is 3 months or prior from Rptdte (6/30/2007) that was Entered (EntryDte Field) in June (m-1) from current month.

[JKing]

Give these a try,,, They're in the same order as the criteria you posted. They may need a little tweaking depending if I understood what you're asking correctly or not.

SELECT Item, rptDate, entryDate, effDate
FROM yourTable
WHERE (([effDate]<=DateSerial(DatePart('yyyy',[rptDate]), DatePart('m',[rptDate])-2,DatePart('d',[rptDate]))) 
And DatePart('m',[entryDate])=Month(now())-1 Or 
DatePart('m',[entryDate])=Month(now())-2);
 

SELECT Item, rptDate, entryDate, effDate
FROM yourTable
WHERE effDate <= DateSerial(DatePart('yyyy',rptDate), DatePart('m', 
rptDate)-3, DatePart('d', rptDate))
AND DatePart('m' ,entryDate) = Month(Now())-1