Help with Dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.

--
Center for Governmental Research
Research/Technology


KARL DEWEY said:
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

KateM said:
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
Click to expand...
Click to expand...
 
KateM said:
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)]
Click to expand...

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
 
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.

KateM said:
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.

--
Center for Governmental Research
Research/Technology


KARL DEWEY said:
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

KateM said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much for your help.
--
Center for Governmental Research
Research/Technology


John W. Vinson/MVP said:
KateM said:
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)]
Click to expand...

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
Click to expand...
 
Back
Top