Dynamic Combo Box with month names

[gavin31]

I have a combo box that has

SELECT DISTINCT MONTH(startTime) FROM tblHours

as the rowsource.

This is great, but only shows the month number
e.g. for January shows 1, February shows 2, etc.

How can I get a 2nd column in the combo box to show the month name e.g. January, February, etc.

I know I can't use the MONTHNAME function, so any other ideas would be greatly appreciated.

I'd like to do this in the rowsource if possible, but VBA code would be ok aswell.

Cheers

Gavin

 

[SgtJarrow]

Try:

SELECT DISTINCT MONTH(startTime), FORMAT(startTime, "mmmm") FROM tblHours

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[gavin31]

mstrmage1768

Brilliant...works perfectly.

Obvious really, should have thought of that.

Thanks

Gavin

 

[PHV]

I know I can't use the MONTHNAME function
I wonder why ...

 

[TheAceMan1]

How are ya gavin31 gavin31 . . .

For starters I parallel [blue]PHV![/blue] and point out this ambiguity! . . .

Time covers a 24hr period. [purple]How could you possibly know what month when a 24hr period can cover any day of any month? . . .[/purple]

The results here are totally [purple]ambiguous! . . .[/purple]

[blue]I hope you see my point! . . .[/blue]

See Ya! . . . . . .

 

[gavin31]

PHV

Don't know why MONTHNAME won't work in the rowsource.
It works in code module, just not in rowsource.

TheAceMan1

I see your point, however...

startTime is a field from tblHours which is a date stored in "General Date" format

The month is stored as part of that field, obviously.

All I am doing is extracting the distinct months from existing records in tblHours.

So I don't think there's any real ambiguity, just a shortcoming in my initial description.

Cheers

Gavin