vbadatems-accesscombobox

How to format a general date to a distinct month in a combobox dropdown list in Access VBA?

I have here a combobox which is getting values from a Date column of a table. So this is how it looks like:

Image1

Is there a way that I can convert the date list of the combobox into distinct months like this below?

Image2

I looked at this method from its answer but this is using Excel and I am on Access and still new to Access.

Help is much appreciated. Thanks in advance.

Solution

  • You can use the following SQL as your RowSource for the ComboBox (see image below):

    SELECT Month([fldDate]) AS MonthNumber, Format([fldDate],"mmmm") AS MonthText
FROM YourTableName
GROUP BY Month([fldDate]), Format([fldDate],"mmmm");

    Your ComboBox should be defined with two columns. Column widths should be 0cm;2cm which will hide the first column. The Bound Column property should be set to 1 so you can reference the user selection simply by using ComboBox.Value. The user can select by month name but you will use the month number (first column) for any additional processing.

    You do not mention whether the table contains data for 1 year only. If you have multiple years then you will need to take that into consideration.

    enter image description here