Date differance in a query!

[mmck1964]

I have a query that has a [Record] and a [Date] field in it. I would like to have an expression in the query that looks at the second records [Date] and subtracts it from the previous [Date] and so on. To look like this: [Diff] is the expression field.

[Record] [Date] [Diff]
1 8/25/02
2 8/26/02 1
3 8/28/02 2
4 8/29/02 1
5 8/30/02 1

Thanks,
mmck1964

 

[tomschafer]

This won't return the very last record but it'll do the trick.

select
a.Record,
a.Date,
datediff(day, a.Date, b.Date)
from
TableName a,
TableName b
where
b.Record-a.Record=1

 

[mmck1964]

How can i write this in the query field?

 

[tomschafer]

While in graphical design mode for the query click the View menu and go to SQL View.

Also, the code I posted will not work in Access97 - It doesn't support sub-queries, Access2K does though. If you need it to work in '97 let me know.

 

[mmck1964]

Great! and Thanks! I have AccessXP. I'll give it a try.

 

[mmck1964]

I cant make it work? I have a query to get the data I want so do I have to make another query to add the expression in? I typed it like I thought I should by replacing Record and Date with the real Field names. Where did I go wrong?
thanks,

 

[Sawatzky]

Dear mmck1964,

what do you need you query for?

I think your on the wrong way by updating(do you??) the table with calculated values.

regards

Astrid

 

[tomschafer]

Did you also change "TableName" to the name of your table?

 

[mmck1964]

I got it to work! Thanks!

 

[hoym]

i have a problem that resemble to MMCK1964. But this time I have to manage " time " instead . I know that is a function "datediff" ; it is any function or work-around that I can manipulate difference of " time ".

 

[grtammi]

See the help file for DateDiff - it'll show you all the different ways you can count days, hours, minutes, seconds, years, etc etc etc...

HTH

Greg
If you don't understand, just nod and smile ...