I have a table with the following fields:
Date, Left Door, LDDiff, Right Door, RDDiff

Each day, the number of people who came to the library will be entered into the Left Door field. So, on Oct. 31, the left door count was 10652 and on November 1, the left door count was 8956. I'd like the LDDiff to display the difference between the the two dates.

---

Reply from Richard Rost:

First, don't use "Date" as a field name. It's a reserved word in Access. You should use "MyDate" or something like that. Trust me.

If you have all consecutive dates in your database, you could create a calculated query field with DLOOKUP like this:

YesterdaysAmount: DLOOKUP("LeftDoor","MyTable","MyDate=#" & MyDate-1 & "#")

This says "look up the value LeftDoor from the MyTable table where the date of that record is equal to ONE DAY BEFORE the date of the current record.

Now you can perform your subtraction.