Calculating between dates on separate records

[Carl]

Hi,

I have a query that produces an output similar to the sample shown
below. These records are based on certain criteria and a date range
selected from a form. I need to create some kind of calcualtion or
record manipulation to do the following:

I need to subtract dates from one another, for example for ID 1 I want
to find the difference between 24-Jun-04 and 25-Jan-06 for the 2
applications for Person1.

Or, I need to find 730 days (2 years) from the newest date and if the
second date is within this range I need the record displayed.

This becomes even trickier if an ID has more than two applications as I
need to use the difference bewtween dates 2 and 3 and so on.

What I basically need is to be able to calculate between 2 dates where
each is on a different record.

Cheers.

ID Forename1 Surname ApplicationNumber FinalDecisionDate
1 Person1FN Person1SN 216869761 24-Jun-04
1 Person1FN Person1SN -761922672 25-Jan-06
2 Person2FN Person2SN 1109951655 06-Apr-05
2 Person2FN Person2SN 1733776761 22-Jul-04
3 Person3FN Person3SN -1502540739 29-Jun-04
3 Person3FN Person3SN 1103313929 11-Mar-05
4 Person4FN Person4SN -501941607 15-Apr-05
4 Person4FN Person4SN -1230039876 22-Feb-06

[afscott]

can you not just use the built in expression "DateDiff()" and reference
each entry using the Forms![Form Name]![Field] ?

Carl wrote:

Hi,

I have a query that produces an output similar to the sample shown
below. These records are based on certain criteria and a date range
selected from a form. I need to create some kind of calcualtion or
record manipulation to do the following:

I need to subtract dates from one another, for example for ID 1 I want
to find the difference between 24-Jun-04 and 25-Jan-06 for the 2
applications for Person1.

Or, I need to find 730 days (2 years) from the newest date and if the
second date is within this range I need the record displayed.

This becomes even trickier if an ID has more than two applications as I
need to use the difference bewtween dates 2 and 3 and so on.

What I basically need is to be able to calculate between 2 dates where
each is on a different record.

Cheers.

ID Forename1 Surname ApplicationNumber FinalDecisionDate
1 Person1FN Person1SN 216869761 24-Jun-04
1 Person1FN Person1SN -761922672 25-Jan-06
2 Person2FN Person2SN 1109951655 06-Apr-05
2 Person2FN Person2SN 1733776761 22-Jul-04
3 Person3FN Person3SN -1502540739 29-Jun-04
3 Person3FN Person3SN 1103313929 11-Mar-05
4 Person4FN Person4SN -501941607 15-Apr-05
4 Person4FN Person4SN -1230039876 22-Feb-06

[Bob Quintal]

"Carl" <ca**********@newcastle.gov.uk> wrote in
news:11**********************@y41g2000cwy.googlegr oups.com:

Hi,

I have a query that produces an output similar to the sample
shown below. These records are based on certain criteria and
a date range selected from a form. I need to create some kind
of calcualtion or record manipulation to do the following:

I need to subtract dates from one another, for example for ID
1 I want to find the difference between 24-Jun-04 and
25-Jan-06 for the 2 applications for Person1.

Or, I need to find 730 days (2 years) from the newest date and
if the second date is within this range I need the record
displayed.

This becomes even trickier if an ID has more than two
applications as I need to use the difference bewtween dates 2
and 3 and so on.

What I basically need is to be able to calculate between 2
dates where each is on a different record.

Cheers.

ID Forename1 Surname ApplicationNumber
FinalDecisionDate 1 Person1FN Person1SN 216869761
24-Jun-04 1 Person1FN Person1SN
-761922672 25-Jan-06 2 Person2FN Person2SN
1109951655 06-Apr-05 2 Person2FN Person2SN
1733776761 22-Jul-04 3 Person3FN Person3SN
-1502540739 29-Jun-04 3 Person3FN Person3SN
1103313929 11-Mar-05 4 Person4FN Person4SN
-501941607 15-Apr-05 4 Person4FN Person4SN
-1230039876 22-Feb-06

Ohkayy. What you need to do is create a new query, with two
copies of your existing query or table. Alias the first as A,
the second as B.

then join A to B on personFN and PersonSN, Put the fields from A
down and the applicationNumber, finaldecisiondate from B as
well.

Now create a new field with the expression
DateDiff("d", a.finaldecisiondate, b.finaldecisiondate)
The criteria for that field should be BETWEEN 0 and 730
You also want to put <> B.applicationNumber as the criterion for
A.ApplicationNumber, so you dont compare a date agains itself.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com