Set Default Value in Combo Box to the Next Saturday

[mahoney.jonathan]

I've built a status report and time tracking tool using Access for our
group. The issue I'm having is we report status based upon the last day
of the week (Saturday).

tblCalendar is setup with the date of every Saturday for the next 10
years in it.
frmTimeSheet has a combo box that reads values from that table
comboWeekEndDate

The issue I have now is, it always defaults to the 1st day in the
table, or a default date if I specify one. What i want to be able to do
is:

Have Access read the current date and find the closest date in
tblCalendar and make it the default value for comboWeekEndDate. (This
should change every week obviously).


Everything else is working great except for the users having the scroll
through the entire list. I haven't been able to figure it out so far
but was hoping someone here would have an idea.

Thanks.

 

[John Vinson]

Have Access read the current date and find the closest date in
tblCalendar and make it the default value for comboWeekEndDate. (This
should change every week obviously).

Set the DefaultValue property of the combo box to

=DateAdd("d", 8 - Weekday(Date()), Date())

to have it always get the next Saturday (e.g. to get October 28 if
today is October 21).

John W. Vinson[MVP]

 

[Jeff Boyce]

Are you saying that you want to see the first date in the table that follows
the current date?

Have you tried using a query? You could put something like:

Date()

as a criterion for the date field, sort Ascending, and set the Unique Values
property to Yes and the Top property to 1 (one). This should return the
single (first) value greater than the current date.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Van T. Dinh]

DateAdd("d", 7-WeekDay(Date(), vbSunday), Date())

always give you the coming Saturday and same date if the day is Saturday.

You can use this as the Default value for your ComboBox (provided that the
BoundColumn is the Saturday dates you mentioned).