Date Selection

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

I have a date field on a form, and I need to restrict the input date to only
Mondays. It's a log form and they want it to be "week starting (only on
Mondays)." And the Monday could be in the past or future, depending on when
they get to data entry.

It would be really neat if a combo box could be used where the only dates
listed were Mondays.

Ideas?

Thx.
 
You can create a combo box that contains only Mondays for the user to choose
from.

Set the combo's RowSourceType property to:
ListMondays

Paste the code into a standard module:

Function ListMondays(fld As Control, ID As Variant, row As Variant, col As
Variant, code As Variant) As Variant
'Modified from Access 97 help file.
Dim intOffset As Integer

Select Case code
Case acLBInitialize ' Initialize.
ListMondays = True
Case acLBOpen ' Open.
ListMondays = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListMondays = 4
Case acLBGetColumnCount ' Get columns.
ListMondays = 1
Case acLBGetColumnWidth ' Get column width.
ListMondays = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intOffset = Abs((9 - Weekday(Date)) Mod 7)
ListMondays = Format(Date + intOffset + 7 * row, "mmmm d")
End Select
End Function

The code is adapted from the Access 97 help, which also contains further
details.

An alternative approach would be to use Weekday() in the AfterUpdate event
of the control, using vbMonday as the first day of the week. Subtract the
day number less 1. If the user enters anything other than a Monday, this
alters the entry to the Monday before the date they entered.
 
That code looks pretty neat!

How could it be "normalized" so that you'd get something like
List(dayofweek)?
 
Hm.

Some issues.

Only four items get listed.

The format does not include the year. I'd like to include the year and be
in the format of short date: 11/11/06.

Thx.
 
The call-back function must have exactly those arguments, and Access will
call it repeatedly as needed. You cannot add additional arguments to the
function and use it in the RowSourceType argument.

Therefore you would need either separate functions for the different days.
If you know for sure that you will not have different controls calling it at
the same time and needing different days, you could use a global variable.
 
Bill take a look at the code and see if you can figure out which line you
might need to change to increase the row count from 4.

You might also be able to work out how to change the line that contains
Format() in order to format the date the way you want.
 
Yep, got that working.

Thanks.

Allen Browne said:
Bill take a look at the code and see if you can figure out which line you
might need to change to increase the row count from 4.

You might also be able to work out how to change the line that contains
Format() in order to format the date the way you want.
Click to expand...
 
One thing I'm stuck on is that the function seems to start with the first
Monday after today.

How/what would I modify so that the first listed Monday was, say, two
Mondays past?

Thanks.

--Bill
 
Subtract 14 from the line that assigns the value:
ListMondays = Format(Date + intOffset - 14 + 7 * row, "mmmm d")
 
Back
Top