Subtract datetime value from the datetive value in previous record

[Billy Rogers]

I have a coworker who has just taken over a report and has asked my help on
simplifying a reporting process. Currently she is copying query data into
excel and adding a forumula column. In the new column the first value is
zero but after that the value of each cell is the value of a datetime column
in the same row subtracted from the same datetime column in the row
above....eg. c2= B2-B1...and then the data is copied back into Access.

I wanted to see if there was a way of doing this without excel. I think
this may have to be done with a recordset using VBA but i wanted to see if
there is a way to do this with a query. I can't seem to think of a way to
do this with a query.

Thanks,


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[John W. Vinson]

I have a coworker who has just taken over a report and has asked my help on
simplifying a reporting process. Currently she is copying query data into
excel and adding a forumula column. In the new column the first value is
zero but after that the value of each cell is the value of a datetime column
in the same row subtracted from the same datetime column in the row
above....eg. c2= B2-B1...and then the data is copied back into Access.

I wanted to see if there was a way of doing this without excel. I think
this may have to be done with a recordset using VBA but i wanted to see if
there is a way to do this with a query. I can't seem to think of a way to
do this with a query.

Thanks,

You'll need a Subquery. There's no such concept in a Table as "the row above"
- a table is an unordered bag of records, not a structured spreadsheet!

Try a calculated field

DateDiff("d", [datefield], NZ(DMax("[datefield]", "[tablename]", "[datefield]
< #" & [datefield] & "#"), [datefield]))

This should give the number of days between the value of datefield in the
table and the most recent value of datefield prior to this record's datefield;
the NZ() stuff will give a difference of 0 for the first record in the table.

If there are other constraints they'll need to be added to the DMax() criteria
expression.

John W. Vinson [MVP]

 

[Billy Rogers]

Am I supposed to leave the # in the formula? or should that be replaced with
something?
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

I have a coworker who has just taken over a report and has asked my help on
simplifying a reporting process. Currently she is copying query data into
excel and adding a forumula column. In the new column the first value is
zero but after that the value of each cell is the value of a datetime column
in the same row subtracted from the same datetime column in the row
above....eg. c2= B2-B1...and then the data is copied back into Access.

I wanted to see if there was a way of doing this without excel. I think
this may have to be done with a recordset using VBA but i wanted to see if
there is a way to do this with a query. I can't seem to think of a way to
do this with a query.

Thanks,

You'll need a Subquery. There's no such concept in a Table as "the row above"
- a table is an unordered bag of records, not a structured spreadsheet!

Try a calculated field

DateDiff("d", [datefield], NZ(DMax("[datefield]", "[tablename]", "[datefield]
< #" & [datefield] & "#"), [datefield]))

This should give the number of days between the value of datefield in the
table and the most recent value of datefield prior to this record's datefield;
the NZ() stuff will give a difference of 0 for the first record in the table.

If there are other constraints they'll need to be added to the DMax() criteria
expression.

John W. Vinson [MVP]