calculate dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lilbit27 - 05 Jul 2007 21:10 GMT

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.

Reply to this Message

jahoobob - 06 Jul 2007 01:23 GMT

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.

>have serval date fields in my table that i need to perform
>calcualations to bring bring a certain # of items.
[quoted text clipped - 17 lines]
>(6/30/2007) that was Entered (EntryDte Field) in June (m-1) from
>current month.

Reply to this Message