Next largest value

[NSAMSA]

All:

I'm currently building a database which has numerous entries for physical formulas for two tanks. Each formula has a start date, but no end date. The end date is actually the next start date for another formula. So, there may be formula HEN010717GG10 with a start date of 1/7/17. I may get an email telling me the formula will be changing on 4/24/17 to HEN042417FF25. 4/24/17 is not put in as the last day of the prior formula, however, the change does make it the last day. Is there a way to make a calculated field that says the last date will be the next largest date in that field, so that the last date automatically populates when I add a new formula?

Thanks in advance for any advice/suggestions.

 

[The_Doc_Man]

I would make a VBA function to extract this date value from your formula as a starting point to any further date computation. If you make a single-valued public function (in a general module) you can call that function from the SQL implied in a DoCmd.RunSQL operation. After that, calculations involving DateAdd or DateDiff functions are easily managed in queries.