asked on

Set ComboBox default value based on current date.

Dear Experts,

I have a combo box on a form with the following date sequence that is pulled in from a table:
2017-2018
2018-2019
2019-2020
2020-2021
2021-2022
etc..

What I would like to do is display as default the value 2018-2019 " IF " the current date is between August 2018 - July 2019 (the school year for us) . Then when the date flips over to August 2019 the default value shown in the combo box will be 2019-2020 until July 2020.

How would I go about doing this?

Thanks in advance!~

Mike

M A

Create a table Period_master
-----------------
PeriodID (Primary Key, AutoNumber)
PeriodName (a description of the period)
PeriodStart (start Date)
PeriodEnd (end date)

Call the PeriodName by a query. Attached sample access file.

SELECT Period_master.PeriodID, Period_master.PeriodName, Period_master.PeriodStart, Period_master.PeriodEnd
FROM Period_master
WHERE (((Period_master.PeriodStart)<=Date()) AND ((Period_master.PeriodEnd)>=Date()));

Open in new window

or
Use a function to call 20172018 as in the below discussion
https://www.experts-exchange.com/questions/26931063/Microsoft-Access-function-return-financial-year-based-on-a-date-value.html
Database1.accdb

Dale Fye

me.combo.Value = Format(DateAdd("m", -7, mydate), "yyyy") & "-" & Format(DateAdd("m", 5, mydate), "yyyy")

HTH
Dale

Mike Rudolph

ASKER

Dale,

When I use your simple code on the On Current event of the form I get "1899-1900" as the default value on the combo box SchoolYear. What am I doing wrong here?

Mike Rudolph

ASKER

MAS,

interesting solution. I tried the table and addd the query and the only one value shows in the combo dropdown. It's the correct value but how do I make it display by default in the combo box (or should I now make it a textbox control) so it displays for the user automatically?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Rudolph

ASKER

Dale,

Yep! That did it! Thanks!

M A

Glad to know you found what you want. :))