calculating date diff in query or sql

[RICH48]

hoping someone can help... I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..
basically the structure is

equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14

What i'm after is the 4th column to work out the date diff... in this case 2 days
the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..

hope this is clear for someone to help out :-)

I've been using access for some time now so not a beginner... or thought i wasn't..... so appreciate any help so i can sleep tonight...



cheers rich48

[ranman256]

dateDiff("D",[START DATE],[END DATE]) 'if you have an end date
OR
dateDiff("D",[START DATE],DATE()) 'up to today

[RICH48]

Hi Ranman yeah tried that but not exactly what i need....



basically we have to work out the number of days between the 2 dates - and i could have like 1 say 20 entries all with dates...
need to calculate the no of days between them all...
so
call 1-2
call 2-3
call 3-4 and so on...

that help.??

rich48

[John_G]

Hi -

That is not something you can do with a query or SQL. Access is not good at making comparisons between records in the way Excel can. Essentially, Access only "knows" about the current record.

What you want to do will require VBA code to step through the records for a specified "equipment number", in the proper order.

John