Forum Discussion
Find next date in table
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
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.
Thanks.
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> 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()")
