Is it possible for me to get the number of days between dates from one column? Like doing a DateDiff between the current field and the one before it. So if my dates were like this:

[Approved Date]
1/1/2006
3/1/2006
5/1/2006
7/1/2006
9/1/2006
11/1/2006

My output would be:
(1/1/2006 - 0)
(3/1/2006 - 1/1/2006)
(5/1/2006 - 3/1/2006)
(7/1/2006 - 5/1/2006)
(9/1/2006 - 7/1/2006)
(11/1/2006 - 9/1/2006)
whatever those values are.

I know I could do this in code, I was just curious if I would be able to do it in a query as it tends to be much faster.

Thank you for your response.

Not real difficult. You can use either a subquery or DMax that gets the largest date that's less than the current record (presuming that is the order the records are in), and do the math between that and the value in the current record.

How would I set up the criteria expression for the DMax()?

Right now I have

Select [Approved Date],DMax("[Approved Date]","Table1")
From Table1
Where DMax("[Approved Date]","Table1")<[Approve Date]

This doesn't work though, so I'm not sure how I am supposed to set it up. (This isn't all the code of my query, this is just a small subset of it using a different table name, the real query has inner joins and more fields.)

Thank you.

I assume you wouldn't want it in the WHERE clause, just in the SELECT:

...DMax("[Approved Date]","Table1", "[Approved Date] < #" & [Approved Date] & "#") AS PrevDate

Watch it, this direction is not technically correct.

In a query, without some extra method of discrimination, you can't do this. In a query, which is based on SQL, "before" and "after" (in the sense of "this record is before that one") has no meaning SYNTACTICALLY. A sort order imposed by an "ORDER BY" clause is a SEMANTICS thing. It doesn't help you in this problem because there is no syntax for "PREVIOUS" or "NEXT" - in a query. It ain't an SQL concept.

You can do this in a form because a form erects a structure AROUND that query and gives you the support for holding "previous" data.

You can also do this in VBA code because you can scan a recordset and erect an infrastructure that allows you to remember "previous" in order to work with it.

Technically, however, you have to cheat like a son-of-a-...gun to do this via SQL.

OK, having now sliced and diced your great ideas, here is one way you do it.

If there is never a case where two times are the same, you can do an UPDATE query to upate a counter showing the number of records lower than the current one. Write these in a field that has no permanent meaning, but that supports imposition of a temporary order based on date sorts. If you had no duplicates, then each count is unique and one greater than its date predecessor, starting from zero and ending at one less than the count of records in the table. After the update, you can try your computation query as a separate step.

OK, now you can do a DLookup for the record with the "arbitrary" number that is one less than that of your current record. You would need special-case SQL testing for the first record and if there is EVER even a SLIM chance for two dates to be the same, you are hosed anyway.

Note: There is a reason that the syntax does not exist for NEXT or PREVIOUS in SQL. In set theory on which SQL is base, the concept of order is not significant to the formation of the answer-set for any query. Order is imposed after the answer set has been formed. It is an afterthought.

Well I ran the query that pbaldy suggested and it did work. It makes sense to get the maximum date found that is less than the date that you have. I had trouble writing the statement (especially since I had more variables to consider than just the date itself), but now that I have more experience with DMax, I was able to do it.

I now have two fields Approved Date and PrevDate
Approved Date Prev Date
8/4/2006
10/3/2006 8/4/2006
11/21/2006 10/3/2006

I will do special casing when the Prev Date is null.

Thank you both for your help in this matter, I was looking across many sources to find an answer and this solution worked for me.