New to DateDiff Function

[bwojcik]

I am trying to create a database in Access using a Table. I'm trying to set
the DateDiff function to calculate the Last Day Worked and Now for every
entry. How and where do I enter with information.

 

[Michel Walsh]

If you need the two dates, side by side, in a singe row, you need to refer
to the table twice, though a join, as example.



SELECT a.dateWork, b.dateWord,
LAST(a.someOtherField),
LAST(b.againSomeOtherField)

FROM (myTable AS a LEFT JOIN myTable As b ON a.dateWork > b.dateWork)
LEFT JOIN myTable AS c ON a.dateWork

c.dateWork

GROUP BY a.dateWork, b.dateWork

HAVING Nz(b.dateWork = MAX(c.dateWork), true)



Note that I used outer join since the very earliest date won't have any
'previous date'. As it is, anything you will refer through the alias b could
be seen as data relevant to the working date before the data you can access
through alias a.


dateWork Event
2001.01.05 "Nothing"
2001.01.06 "Again, nothing
2001.01.12 "Now, something""


should, with

SELECT a.dateWork, b.dateWork, LAST(a.event), LAST(b.event) FROM ...


produce

2001.01.05 null nothing null
2001.01.06 2001.01.05 again nothing nothing
2001.01.12 2001.01.06 now something again nothing




Hoping it may help
Vanderghast, Access MVP