Find next date in table

[karenlorr_uk]

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

[tbl_dates]![my_date]
01/02/2024
28/06/2035
17/04/2024
24/12/2021
23/05/2022

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

Thanks

[#2]

@karenlorr_uk 

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.

Thanks.

[#3]

@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
e.g.
=DMax("my_date","tbl_dates")
=DMin("my_date","tbl_dates")

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

[#4]

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"?

[#5]

@karenlorr_uk 

> 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()")