Calculating field for Previous Processing Day

[David]

I have a table that includes production data for each day of
processing, allowing me to compare totals from any two dates where
processing occurred.

My plan is to create one table with the most recent data using the
formula (Max[Prod Date])

I need to create another table for the previous processing date and
since that might be 1,2 and even 3 days earlier, the formula ((Max
[Prod Date])-1) doesn't work as it would often select a date where no
file exists.

Any suggestions for a formula that would pick up the next most recent
date?

 

[KARL DEWEY]

Best not to use tables but queries instead like this --
qryLastProdDate
SELECT Max(Production.ProdDate) AS LastProdDate, Sum(Production.QTY) AS
Total_Prod
FROM Production, qryLastProdDate;

SELECT Max(Production.ProdDate) AS LastProdDate, Sum(Production.QTY) AS
Total_Prod
FROM Production, qryLastProdDate
WHERE Production.ProdDate < qryLastProdDate.LastProdDate;