Question Selecting Drop Down Value with closest date

[mcclunyboy]

Hi,

I have a drop down list populated by a query as below:

SELECT id, name, name_start_date FROM tbl_name ORDER BY name_start_date DESC;

I would like this list to still be available but the default value to select the closest id for the record with the name_start_date that is nearest the current date (but in the future).

I hope that makes sense, I've been experimenting with the Default Value property and the expressions using Date Diff but I am quite literally stuck!..pesky!

Thanks

 

[CJ_London]

think I understand - if your data is

id........name.....namestartdate
1.........fred.......01/01/2018
4.........harry.....01/01/2017
3.........George..01/01/2016

you want the default value to be 4

before trying to provide a solution, is this correct?

also, is the listbox unbound?

and you do know that name is a reserved word? using it can produce unexpected results

 

[CJ_London]

Sorry, the other question is

What happens if you have two ID's with the same start date?

 

[mcclunyboy]

Hi,

Thanks for the reply.

Yes you are correct with your data representation.

You are also correct about the name field, in reality I used something else but I wanted to make the question easy to understand (apologies I shouldn't have used "name" here).

The listbox is bound, the ID is used to populate a field in a new record created by the form.

Finally the listbox records could theoretically have 2 or more rows with the same start date but if they did it would be user-error when completing another part of the database. I'll add some validation to solve this as theoretically it shouldn't happen (it is essentially 1 date per month and I want to be able to save the user time when completing the form by automatically selecting the next value as default, this will be correct 99% of the time).

I really hope that makes sense.

Thanks

 

[CJ_London]

I used something else but I wanted to make the question easy to understand

always better to use the real name or at least explain you are using generic names - so often it can hide the real problem or requires unnecessary additional investigation.

try something like this in the listbox default value property

=dlookup("ID","tbl_name","name_start_date = dmin('name_start_date','tbl_name','name_start_date >Date()')")

note the use of single quotes for dmin because it is within the criteria of the dlookup. alternatively

=dlookup("ID","tbl_name","name_start_date = " & dmin("name_start_date","tbl_name","name_start_date >Date()"))

 

[mcclunyboy]

Great!

Thanks.