difference dates

[Guest]

Hi
I have a query whith Col-A, Col-B and Col-C. How can I do a difference(dates
Col-B(2nd row) and Col-C(1st row), …B(3th) and C(2nd),…. etc ) in Col-D?

Col-A Col-B Col-C Col-D (difference)
Xpto(1st row) 15-Mar-2006 16-Mar-2006 -
Abc 18-Mar-2006 20-Mar-2006 2 (diff 18-Mar and
16-Mar)
Dfd 23-Mar-2006 25-Mar-2006 3 (diff 23-Mar and
20-Mar)
….

thanks

 

[Allen Browne]

Use DateDiff() to get the difference between 2 dates.

Assuming you want the difference in days, type an expression into the Field
row in query design, like this:
DateDiff("d", [Col-B], [Col-D])
and so on for other differences you want to see.

If you have lots of date fields repeating in the same table, chances are
that this table should be broken into two. The new table will have a record
(not a field) for each date, and you can use another field to distinguish
whatever is the difference between col B, C, and so on.

So the new table will have fields:
ForeignID relates to whatever is in our Col-A.
TheDate Date/Time. The date value.
DateType Whatever it is that distinguishes B from C from D etc.

 

[Douglas J Steele]

Not quite sure that's what Sérgio's looking for, Allen.

It sounds to me as though he wants to compare the value in Col-B of the
current row to the value in Col-C of the previous row.

As you know, that's not particularly easy to do in Access. One way is to
have a computed field that returns the value of the previous row to the
current row, but that's almost always very inefficient.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Use DateDiff() to get the difference between 2 dates.

Assuming you want the difference in days, type an expression into the Field
row in query design, like this:
DateDiff("d", [Col-B], [Col-D])
and so on for other differences you want to see.

If you have lots of date fields repeating in the same table, chances are
that this table should be broken into two. The new table will have a record
(not a field) for each date, and you can use another field to distinguish
whatever is the difference between col B, C, and so on.

So the new table will have fields:
ForeignID relates to whatever is in our Col-A.
TheDate Date/Time. The date value.
DateType Whatever it is that distinguishes B from C from D etc.

 

[Guest]

thanks for your help..

but in this case the values or dates are in diferent rows in the query. the
Function Diff doesn't work in this case.

--
Sérgio Aires
Lisboa
Portugal


"Douglas J Steele" escreveu:

Not quite sure that's what Sérgio's looking for, Allen.

It sounds to me as though he wants to compare the value in Col-B of the
current row to the value in Col-C of the previous row.

As you know, that's not particularly easy to do in Access. One way is to
have a computed field that returns the value of the previous row to the
current row, but that's almost always very inefficient.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Use DateDiff() to get the difference between 2 dates.

Assuming you want the difference in days, type an expression into the Field
row in query design, like this:
DateDiff("d", [Col-B], [Col-D])
and so on for other differences you want to see.

If you have lots of date fields repeating in the same table, chances are
that this table should be broken into two. The new table will have a record
(not a field) for each date, and you can use another field to distinguish
whatever is the difference between col B, C, and so on.

So the new table will have fields:
ForeignID relates to whatever is in our Col-A.
TheDate Date/Time. The date value.
DateType Whatever it is that distinguishes B from C from D etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi
I have a query whith Col-A, Col-B and Col-C. How can I do a
difference(dates
Col-B(2nd row) and Col-C(1st row), .B(3th) and C(2nd),.. etc ) in Col-D?

Col-A Col-B Col-C Col-D
(difference)
Xpto(1st row) 15-Mar-2006 16-Mar-2006 -
Abc 18-Mar-2006 20-Mar-2006 2 (diff 18-Mar and
16-Mar)
Dfd 23-Mar-2006 25-Mar-2006 3 (diff 23-Mar and
20-Mar)
..

thanks