Thanks for your reply.
Perhaps I didn't explain this properly. I am not looking to find elapsed time between the entry of records.
What I am looking to do is determine the number of days between the occurrence of each event. This is a table which records daily rainfall and I am trying to determine the longest period of drought during the 10 year period that data has been captured.
In other words, what is the maximum difference between each consecutive date in the table/query.
Thanks
The underlying problem is that access has no concept of a next/previous record so things you can easily do in excel are not possible in access. The reason there is no next/previous record is because what you really have is an unordered set of records, that just happen to display in a particular (or more correctly no particular) order. If you had a calculation based on a "previous record", and then resorted on a different field, your existing calculation mechanism would no longer have any meaning for the new order. For a large set of results, evaluating the differences between successive records each time might be too slow a process, so it's something you need to test and evaluate.
So with a database, it's better to find a way to work with the SET of records as a whole, (if you can) and not bother about the order, or more correctly the comparison between successive records.
So one way you can do this is to take a temporary copy of the records and store that in a different table, including a record order counter. Now you could iterate the records in the temporary copy, in the required sort order, one at a time and store the time differences in that table. And now you can sort the records on the elapsed period, and easily analyse the frequency, as well as restore the original order if you need to do so.
You could also prepare a report, because a report is necessarily ordered, and calculation based on successive records become available, but that probably wouldn't help you find the largest gap.
Another way is to anticipate that you might need the date and time of the previous record, so when you store a new record in the live table, you store information in the new record about the previous record. You have to bear in mind now that deleting and inserting records in the middle of your data will invalidate the stored data, but carefully controlled, it's another solution.
So you could add a new field to your table to include time of last reading, or time since last reading, and iterate all of your records to evaluate that field for all of your records. So it's a one-time process, but now each row of data holds the elapsed time between readings. It doesn't identify the previous reading, but it does hold the time. You could have another field to identify the ID of the previous reading. This would be far more efficient than trying to re-evaluate the sort order of mainly static data each time you need the information, but may not be 100% reliable because of the effect of inserts/deletions and edits.
I hope this helps.