Creating combobox with unique Month and year entries from a date field in a table

[sponge]

Hi,

Anyone know how to create a combo box that has unique month and year entries from a table (month in one column and year in another - i.e. 2 columns)?

I've been able to get the textbox of the combobox to output the correct format using a custom format but it does not affect the combobox data. Also, I'm not sure how to separate this by two columns...

Any help would be much appreciated!

 

[WayneRyan]

Sponge:

The only way that I can see is using two queries:

SELECT DISTINCT (Format([TestDate],"mm-yyyy")) AS NewClm
FROM tblDates;

NewClm
=======
01-2004
01-2005
02-2004
03-2004
04-2004

SELECT Mid([NewClm],InStr(1,[NewClm],"-") + 1) AS TheYear, 
       Mid([NewClm],1,InStr(1,[NewClm],"-") - 1) AS TheSortMonth, 
       Format(CDate([TheSortMonth] & "/1/" & [TheYear]),"mmm") AS TheDisplayMonth
FROM Query1
ORDER BY Mid([NewClm],InStr(1,[NewClm],"-") + 1), 
         Mid([NewClm],1,InStr(1,[NewClm],"-") - 1);

TheYear TheSortMonth TheDisplayMonth
======= ============ =============
2004	01	     Jan
2004	02	     Feb
2004	03	     Mar
2004	04	     Apr
2005	01	     Jan

The combo only uses TheDisplayMonth and TheYear, the other column just
provides the right sort order.

Wayne

 

[sponge]

Hi,

Thanks for the reply.

How would I get this to work with the combo box? I tried using the row source as "Query2" but to no avail.

I was originally wondering if there was some way to manipulate the Year() and Month() functions from VBA into SQL to get this to work...