Query for previous field value

  • Thread starter Thread starter Dean
  • Start date Start date
D

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
 
Do you have a field for previous records date field? If so you can use
Datediff("d",[previous date field],[current date field])
 
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.

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

Dean said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top