A Date list box for Month selection and separte one for year sele.

[cgark]

I would like to put a month picker and a year picker on a data entry form for
easy entries. It needs to be to the same field if possible. It is like what
you see on the web sites when giving information and the dates are need to be
chosen, it is usually by month and then a year is picked. Is there a way? We
are using the calendar object now, but it would be nice since we need only
the month and year data for our records, to be able to just pick that only.
Thank you

 

[Graham Mandeno]

If you really only want to store the month and year, then I think a
date/time field is overkill. As well as being twice the size (not really a
problem) you will need to be careful that the date is always the first of
the month and that there is no time part, otherwise filtering will become
difficult.

I suggest you encode the month and year in a single long-integer field in
the form yyyymm. For example, 200609 would be September 2006.

For date selection of the type you describe, you can use two combo boxes
with RowSourceType set to Value List.

In the year selector, set the rowsource to a list of valid years - for
example:
"1998;1999;2000;2001;2002;2003;2004;2005;2006;2007;2008;2009"

In the month selector, set the ColumnCount to 2 and set the RowSource to:
"1;January;2;February..." etc
Set ColumnWidths to 0 to hide the left column.

You can then use the AfterUpdate events of both combo boxes to update the
field value:

MonthField = Val(cboYear) * 100 + Val(cboMonth)

The RowSource string for the year could be generated in code using a start
and end year;

Actually, I find those combo boxes for selecting a year on websites to be a
pain, especially if the list is very long.

Another idea is to use a textbox and write AfterUpdate code to change a
2-digit year into 4 digits.

Something like this:

Public Function txtYear_AfterUpdate()
If txtYear < 100 then
txtYear = Year(DateSerial(txtYear, 1, 1 ))
End If
End Function

Finally, if you go for the number field in yyyymm format, you will find
these additional functions useful:

Public Function DateToMonth( dt as Date ) as Long
' convert any date to yyyymm
DateToMonth = CLng(Format( dt, "yyyymm" ))
End Function

Public Function FormatMonth( vMonth as Variant, sFormat as String ) as
String
' format a yyyymm value in a given format
' e.g. FormatMonth( 200609, "mmmm yy") gives "September 06"
If IsNumeric( vMonth ) then
FormatMonth = Format( DateSerial( vMonth \ 100, vMonth mod 100, 1),
sFormat)
End If
End Function