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
