Subtracting the same field

[John]

Hi
I have a form with a date field, in this form there are numerous records
with different dates.
In my report I want to subtract the last entered date from the previous
entered date to see how many days have passed.
How do I do this? - basically subtracting the same field from itself.

Regards
John

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us

 

[John]

Thanks.
The thing is that the id field does not follow, because I may use the first
record and the middle record, for instance - I get my report from a query.

I was even thinking of using a 'make table query' to make a table with the
id field, but how can I make a table with a id (autofield) field?
Or is there any other way.

Thanks
John

 

[Allen Browne]

If you can define the order of the records you want, then you can use a
subquery to get the value. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

You could also use an extended version of DLookup() that allows you to
specify the order:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html

If nothing else works, a temp table is an option. Instead of a Make Table,
use an Append. Create the table with the fields you want, including the
AutoNumber. Kill all the existing records:
dbEngine(0)(0).Execute "DELETE FROM Table1;", dbFailOnError
and then populate it with an Append query statement.

 

[John]

Thank you very much. I GOT IT RIGHT - it works.

Regards
John