Using MS Access/Wildcard Dates from a Combo Box
One to throw really left of field! I would like a form to have three components; [CboMonth], [StartDate] and [EndDate].
My goals are twofold:
The first challenge is how to work out what to populate CboMonth with, and I'd like my choices to be months from current month September 2014 backwards 6 months or maybe 12 months maximum.
The second challenge is how to then populate [StartDate] and [EndDate] with the first and last dates of whatever selected month is chosen with CboMonth.
I know how to populate these fields using today or Date() as the starting point. I am not sure how to get a combo box to help me along.
Can you think of a solution I can start working with?
thanks very much in anticipation mate
For the first challenge the easiest way would be to maintain a table of dates. A one field table with a date field holding the first day of the month (i.e. Sept 1, 2014). Make that a PK. You could have a function that you run on startup to add the current month:
Dim strSQL As String
strSQL = "INSERT INTO tblDates (StartDate) VALUES(#" & DateSerial(Year(Date()),Month(Date()),1) & "#);
This will add a record only if it doesn't exist.
From there you combobox RowSource is easy:
SELECT Format(StartDate,"mmm") AS StartMonth, StartDate FROM tblDates
That should only show the last 6 months (you may need to change it to -7 to get 6 full months)
You also want to hide the second column.
Then you can use the After Update event of the combo to populate the text boxes like so:
Me.txtStartDate = Me.cboMonths.Column(1)
Me.txtEndDate = DateSerial(Year(Me.txtStartDate),Month(Me.txtStartDate)=1,0)
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA