How to sort months in combo box?

[KK]

I have a table which has a field called "Time Period". In these fields,

there are a bunch of months ie. January, February etc.

How can I sort it so that when I create a combo box that pulls up
distinct Time Period values, it shows up as "January, February, April,
June" instead of "April, February, January, June"??

 

[Duane Hookom]

You should be storing the month number which could be used to sort
correctly.
What is the Row Source of your combo box?

If you prefer to store the month name, you could use a value list and enter
"January","February","March","....."

 

[KK]

My row source is Time Period (all those months)

What's this value list you mentioned...

 

[Duane Hookom]

A combo box can be of several Row Types. One is "Value List".

 

[KK]

Oh that one. No, that's not what I need to do.

What I'm trying to do is query Time Period values from my table. But I
need it sorted out in the combo box.

 

[Guest]

Add a another field in your table Time Period (number - integer) named Period
and put 1 through 12 according to the month.

In your Row Select edit it to add ORDER BY Period just before the
semicolon.

This will sort your list.

 

[KK]

I have a form in which I select a month and it is put into a table.

Then I have another form which has a combo box that pulls up distinct
months from the table using a query. I don't want a value list with a
full set of months... because I want the user to know that only certain
months exist in the table ie. March, September, October etc.

I need those months sorted.

I see what you mean but that would mean in the very 1st form, I'd have
to write some code to add the "number" as I add an entry ie.

If [month] = "January" then [month code] = 1
elseif [month] = "February" then [month code] = 2 etc etc

Doesn't seem efficient to me but I was wondering if there are
alternatives out there. Thanks for the ideas though.

 

[Guest]

No code necessary. I thought that you had a table named Time Period that was
used as Row Source for the ComboBox. Now I understand you have a query
between the table and the ComboBox.
If this is true then add the number field in the table, add
the number field in the query, and add to the Row Source SELECT statement
like I suggested.

If the above is not true then WHAT is actual location of your
list of months that appear in the ComboBox that you want to display in the
correct calendar order?

I have a form in which I select a month and it is put into a table.

Then I have another form which has a combo box that pulls up distinct
months from the table using a query. I don't want a value list with a
full set of months... because I want the user to know that only certain
months exist in the table ie. March, September, October etc.

I need those months sorted.

I see what you mean but that would mean in the very 1st form, I'd have
to write some code to add the "number" as I add an entry ie.

If [month] = "January" then [month code] = 1
elseif [month] = "February" then [month code] = 2 etc etc

Doesn't seem efficient to me but I was wondering if there are
alternatives out there. Thanks for the ideas though.

Add a another field in your table Time Period (number - integer) named Period
and put 1 through 12 according to the month.

In your Row Select edit it to add ORDER BY Period just before the
semicolon.

This will sort your list.

 

[Granny Spitz via AccessMonster.com]

I have a table which has a field called "Time Period". In these fields,

there are a bunch of months ie. January, February etc.

How can I sort it so that when I create a combo box that pulls up
distinct Time Period values, it shows up as "January, February, April,
June" instead of "April, February, January, June"??

Create a new query, Insert | Query | OK | Close, then View | SQL View and
paste this into the box:

SELECT CInt(Format(CDate([Time Period] & "-1-2006"), "m")) AS Mo, [Time
Period]
FROM tblPayments
ORDER BY 1;

Replace tblPayments with the name of your table. Save the query as
qryTimePeriods. Create your combo box and use qryTimePeriods as the
rowsource, 0";0.5" for column widths and 2 for column count.

 

[Granny Spitz via AccessMonster.com]

I just read your other post and it sounds like you may have duplicate months
in your table. If that's the case use this for your query instead (beware of
line wrap):

SELECT DISTINCT CInt(Format(CDate([Time Period] &
"-1-2006"), "m")) AS Mo, [Time Period]
FROM tblPayments
ORDER BY 1;

Replace tblPayments with the name of your table.