Hi,
There may be better ways of doing this but here I am using a subquery aliased as 'a' to select and format dates into month and year whilst also selecting the date I want displayed and in the outer query I am grouping and sorting the values to coerce the results to the desired affect.
- select a.DisplayDate, a.DisplayYear, a.DisplayMonth FROM (
-
SELECT Format([INVOICE_DATE],"mm-yyyy") AS DisplayDate, Format([INVOICE_DATE],"mm") AS DisplayMonth, Format([INVOICE_DATE],"yyyy") AS DisplayYear
-
FROM Invoices
-
) as a GROUP BY a.DisplayYear, a.DisplayMonth, a.DisplayDate
-
ORDER BY a.DisplayYear, a.DisplayMonth
-
You would use a query such as this in the rowsource of an unbound combobox and set it to use bound column 1.
Apologies, I realise my answer does not cover the full scope of the question but I hope it points you in the right direction. You would need to add a where condition to the subquery which you would build dynamically and change the rowsource of the combobox and refresh it. I can look at that later on if you do not understand how to do that.
Gaz