If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A Date list box for Month selection and separte one for year sele.
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 |
#2
|
|||
|
|||
A Date list box for Month selection and separte one for year sele.
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 -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "cgark" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|