Difference Dates

[Guest]

Hi
I have a query whith Col-A and Col-B. How can I do a difference(dates
Col-B) in Col-C?

Col-A Col-B Col-C (dufference)
Xpto (1st row) 15-Mar-2006 -
Abc 18-Mar-2006 3
Dfd 19-Mar-2006 1
….

thanks

 

[Guest]

Hi Sergio - in the Query enter a name in the Field line of an empty column,
then either just subtract one from the other or use the DateDiff function.
The DateDiff function gives you some addition ways of figuring the difference
between the 2 dates.

For example : Diff:[Col-A] - [Col-B]
or
DateDiff(m,[Col-A] - [Col-B])

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Look up DateDiff in the Access help files.

Hope this helps - Yours Dika

 

[Guest]

Hi Kernow Girl
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


"Kernow Girl" escreveu:

Hi Sergio - in the Query enter a name in the Field line of an empty column,
then either just subtract one from the other or use the DateDiff function.
The DateDiff function gives you some addition ways of figuring the difference
between the 2 dates.

For example : Diff:[Col-A] - [Col-B]
or
DateDiff(m,[Col-A] - [Col-B])

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Look up DateDiff in the Access help files.

Hope this helps - Yours Dika

Hi
I have a query whith Col-A and Col-B. How can I do a difference(dates
Col-B) in Col-C?

Col-A Col-B Col-C (dufference)
Xpto (1st row) 15-Mar-2006 -
Abc 18-Mar-2006 3
Dfd 19-Mar-2006 1
….

thanks

 

[Guest]

Sorry Sergio - I see what you mean now, it's kind of a rolling difference.
You could do this in a report or on a form, but not on the Query itself. Do
you need it in specifically in the Query?

Hi Kernow Girl
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


"Kernow Girl" escreveu:

Hi Sergio - in the Query enter a name in the Field line of an empty column,
then either just subtract one from the other or use the DateDiff function.
The DateDiff function gives you some addition ways of figuring the difference
between the 2 dates.

For example : Diff:[Col-A] - [Col-B]
or
DateDiff(m,[Col-A] - [Col-B])

yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
Look up DateDiff in the Access help files.

Hope this helps - Yours Dika

Hi
I have a query whith Col-A and Col-B. How can I do a difference(dates
Col-B) in Col-C?

Col-A Col-B Col-C (dufference)
Xpto (1st row) 15-Mar-2006 -
Abc 18-Mar-2006 3
Dfd 19-Mar-2006 1
….

thanks

 

[John Spencer]

In order to do this you need to have some way to order the columns. If the
Order is the data field, then

SELECT ColA, ColB, ColC, DMAX("ColB","YourTableOrQuery","ColB<#" & ColB &
"#") as PriorDate
, DateDiff("d",ColB,DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "#") )
FROM YourTableOrQuery

You could also do this with a subquery.
SELECT ColA, ColB, ColC
, (SELECT Max(ColB)
FROM YourTableOrQuery as Temp
WHERE Temp.ColB<YourTableQuery.ColB) as PriorDate
, DateDiff("d",ColB, (SELECT Max(ColB)
FROM YourTableOrQuery as Temp
WHERE Temp.ColB<YourTableQuery.ColB))
FROM YourTableOrQuery

 

[Guest]

thanks a lot
your coud is working

.... but in my question I forgot to ask.
I'd like to join vlues in colun A
--
Sérgio Aires
Lisboa
Portugal


"John Spencer" escreveu:

In order to do this you need to have some way to order the columns. If the
Order is the data field, then

SELECT ColA, ColB, ColC, DMAX("ColB","YourTableOrQuery","ColB<#" & ColB &
"#") as PriorDate
, DateDiff("d",ColB,DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "#") )
FROM YourTableOrQuery

You could also do this with a subquery.
SELECT ColA, ColB, ColC
, (SELECT Max(ColB)
FROM YourTableOrQuery as Temp
WHERE Temp.ColB<YourTableQuery.ColB) as PriorDate
, DateDiff("d",ColB, (SELECT Max(ColB)
FROM YourTableOrQuery as Temp
WHERE Temp.ColB<YourTableQuery.ColB))
FROM YourTableOrQuery

 

[John Spencer]

I'm sorry, but I don't understand what you mean by "Join Values in Colun A".
Perhaps you could create and post a sample of the data you would like to
see.

Do you want ColA from both records, along with the calculated difference of
Col B from both records?

thanks a lot
your coud is working

... but in my question I forgot to ask.
I'd like to join vlues in colun A
--
Sérgio Aires
Lisboa
Portugal


"John Spencer" escreveu:

 

[Guest]

Your code to do this…
(your code)
Col-A Col-B PriorDate Col-C
Xpto 15-Mar 18-Mar 3
Xpto 18-Mar 19-Mar 1
Abc 19-Mar 23-Mar 4
Dfd 23-Mar 24-Mar …
Ttr 24-Abr 27-Abr
Ttr 27-Abr ….
Ttr 30-Abr


But I’m trying to do this

Col-A Col-B PriorDate Col-C
Xpto 15-Mar 18-Mar 3
Xpto 18-Mar
Abc 19-Mar 21-Mar 2
Abc 21-Mar
Dfd 23-Mar
Ttr 24-Abr 27-Abr 3
Ttr 27-Abr 30-Abr 3
Ttr 30-Abr 03-Mai 6
Ttr 03-Mai


Ok. This works until the value in
col-A changes because it refers to
another item and values can’t be
mixed. When the value in col-A
changes, PriorDate and Col-C,
must remain blank and go to the
next. This procedure repeats till
the end

--
Sérgio Aires
Lisboa
Portugal


"John Spencer" escreveu:

 

[John Spencer]

SELECT ColA, ColB, ColC
, DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "# AND ColA = """ & ColA
& """") as PriorDate
, DateDiff("d",ColB,DMAX("ColB","YourTableOrQuery","ColB<#" & ColB & "# AND
ColA = """ & ColA & """") )
FROM YourTableOrQuery

You could also do this with a subquery.
SELECT ColA, ColB, ColC
, (SELECT Max(ColB)
FROM YourTableOrQuery as Temp
WHERE Temp.ColB<YourTableQuery.ColB
And Temp.ColA = YourTable.ColA) as PriorDate
, DateDiff("d",ColB, (SELECT Max(ColB)
FROM YourTableOrQuery as Temp
WHERE Temp.ColB<YourTableQuery.ColB
And Temp.ColA = YourTable.ColA))
FROM YourTableOrQuery

 

[Guest]

thanks a lot

ti is working perfectly

--
Sérgio Aires
Lisboa
Portugal


"John Spencer" escreveu:

 

[Guest]

Hi,
The solution below is useful but I have an addtional problem. My Problem is
similar to the one in this thread except the fact that for a value in colum A
there can be same value in column B That is

Col-A Col-B Col-C
Xpto 15-Mar 1
Xpto 18-Mar 3
Xpto 18-Mar 0
Xpto 14-Mar
Abc 19-Mar
Abc 21-Mar 2
Abc 23- Mar 2
Dfd 23-Mar
Ttr 24-Abr
Ttr 27-Abr 3

Desired output are shown in column C. I tried replacing "<" in Dmax by "<="
but it does not seem to work.
I would appreciate any help

Thanks
Satyam