Query for previous field value

[Dean]

I have a query that includes a date field. I need to use the previous
records date field in a calculation to find the days between the current
record date field and the previous record date field. In a table, I could use
the CTRL + ' . How would I have a query look up the previous record date
field?

Dean

 

[Golfinray]

Do you have a field for previous records date field? If so you can use
Datediff("d",[previous date field],[current date field])

 

[Dean]

I want to have the Previous Date Field lookup to the previous record Current
Date Field. Then you are correct, I can use the Datediff function. I don't
want to manually enter the Previous Date Field each time.

Do you have a field for previous records date field? If so you can use
Datediff("d",[previous date field],[current date field])

I have a query that includes a date field. I need to use the previous
records date field in a calculation to find the days between the current
record date field and the previous record date field. In a table, I could use
the CTRL + ' . How would I have a query look up the previous record date
field?

Dean

 

[John Spencer (MVP)]

It would help if you included the SQL (Menu: View: SQL) of the existing query.

Generically, you could use a sub-query to get the prior date.

SELECT dtDate
, (SELECT Max(dtDate) FROM YourTable as TEMP WHERE TEMP.DtDate<
YourTable.dtDate) As PriorDate
FROM YourTable

Or you can use the DMax function
SELECT dtDate ,
DMax("dtDate","YourTable","DtDate<#" & Format(DtDate,"yyyy-mm-dd") & "#")
FROM YourTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County