calculate dates

[lilbit27]

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.

 

[jahoobob via AccessMonster.com]

You don't need to put the RptDte in the table since, as you say, it will
always be the last date of the previous month. To determine the last of 2
months prior to last months last day use this calculation:
=DateSerial(Year(Date()), Month(Date())-2,0)
Substitute a -3 for -2 to determine 3 months prior and substitute > for the =
to use in your criteria for the dates in your table.
How are you storing multiple dates for the same record? You should have only
one EntryDte and one EffDte date for each record.