Find next date in table

Copper Contributor

If today is 25th December 2021
and I have a table with these dates


How can I populate a textbox on a form with the next (not the closest) date in the table?
* It should be 23/05/2022



4 Replies



What rule applies to the generation of those dates? We can probably guess, or make an assumption, about how you did that, but it is also probably more effective to state the rule directly.


@George Hepworth 

They are just dates stored in a date field in a table.

It's simple to do in a query
SELECT TOP 1 tbl_dates.My_date
FROM tbl_dates
WHERE (((tbl_dates.My_date)>Date()))
ORDER BY tbl_dates.My_date;

Or in a form you can use
DMin("my_date", "tbl_dates", "my_date > Date()")

But I was hoping for a form textbox simple expression

Mind you it may not be possible.  I'm sure there isn't a DNext

Let's try a different approach, then.

What do YOU mean when you ask for "the Next Date"? Are you looking for a way to generate another date, based on the pattern so far?
Or are you really asking,
"Looking at the record for today's date, what is the subsequent date, available IN THIS TABLE ALREADY, following that date"?


> I was hoping for a form textbox simple expression

There is. The expression to use as ControlSource is:

=DMin("my_date","tbl_dates","my_date > Date()")