Help with Dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KateM - 30 Sep 2005 13:56 GMT

I need to subtract the date from Appearances in Court from the next date in
appearances in court. Below is what I tried but it just gives 0 since it
isn't taking the next date. What am I missing?

# of Days: [Defendant Stats with Dates]![APPEARANCES IN COURT
(Dates)]-[Defendant Stats with Dates]![APPEARANCES IN COURT (Dates)]

Thanks so much,

Kate

Signature

Center for Governmental Research
Research/Technology

Reply to this Message

KARL DEWEY - 30 Sep 2005 16:01 GMT

What you have is a date subtracted from itself. Of course that leaves zero.

You have to subtract two different dates. Last appearance -- Next appearance

> I need to subtract the date from Appearances in Court from the next date in
> appearances in court. Below is what I tried but it just gives 0 since it
[quoted text clipped - 6 lines]
>
> Kate

Reply to this Message

KateM - 30 Sep 2005 17:06 GMT

Yes, I mention that in the post.
I don't know how to get to the next appearance of the date. There are many
appearances in court and I need to know based on my Name field the next court
date from the first.

Signature

Center for Governmental Research
Research/Technology

> What you have is a date subtracted from itself. Of course that leaves zero.
>
[quoted text clipped - 10 lines]
> >
> > Kate

Reply to this Message

KARL DEWEY - 30 Sep 2005 18:54 GMT

Try using two queries. The first query finds the MAX date. The second query
uses the table and first query. Use criteria less than the MAX date.

> Yes, I mention that in the post.
> I don't know how to get to the next appearance of the date. There are many
[quoted text clipped - 15 lines]
> > >
> > > Kate

Reply to this Message

John W. Vinson/MVP - 30 Sep 2005 18:40 GMT

>I need to subtract the date from Appearances in Court from the next date in
> appearances in court. Below is what I tried but it just gives 0 since it
> isn't taking the next date. What am I missing?
>
> # of Days: [Defendant Stats with Dates]![APPEARANCES IN COURT
> (Dates)]-[Defendant Stats with Dates]![APPEARANCES IN COURT (Dates)]

You might need to try a "Self Join" query. Create a query adding the table
to the grid *twice*, joining by the DefendentID. You'll need to apply some
criteria to the date field to select the needed two dates. For instance, if
you want to pick the next date after the date in a selected record, you
could use a criterion such as

= (SELECT Min[Dates] FROM [Defendent Stats with Dates] AS X WHERE
X.DefendentID =[Defendent Stats with Dates].DefendentID AND X.[Dates] >
[Defendent Stats with Dates].[Dates])

John W. Vinson/MVP

Reply to this Message

KateM - 30 Sep 2005 19:34 GMT

Thank you very much for your help.

Signature

Center for Governmental Research
Research/Technology

> >I need to subtract the date from Appearances in Court from the next date in
> > appearances in court. Below is what I tried but it just gives 0 since it
[quoted text clipped - 14 lines]
>
> John W. Vinson/MVP

Reply to this Message