Days between two Move Dates

[ragu111]

In my table is having data as below:

Box_No Move Date
A12 In 10/02/2006
A12 Out 15/02/2006
A15 In 12/02/2006
A15 Out 25/02/2006
….
….


I need to calculate the No of Days between 2 moves, line below

Box_No In_Date Out_Date Days
A12 10/02/2006 15/02/2006 5
A15 12/02/2006 25/02/2006 13

How can I do this in my MS Access report?

Ragu

 

[Crowley16]

do it in the underlying query...

join the table with itself, one side with the IN dates, the other side with the OUT dates, and then subtract the two

something along the lines of:

select t1.date - t2.date
from t1 inner join t2 on t1.id = t2.id
where t1.move = in and t2.move = out

you'll need to get the syntax correct...

--------------------
Procrastinate Now!

 

[SilentAiche]

Ragu111,

When you get a chance, please search the TT forums for
"date" discussions.

Date issues have been dicussed here at length. I think you'll find the answer you're looking for.

Best Wishes!
Tim

[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]

 

[ragu111]

Dear Crowley

thanks for your help, i'm getting the day different correctly. but i need to have the Box Number also along with days...

Ragu