Custom Sort?

[gomikem]

I have a drop down list in one of my forms that lists shirt sizes. In the shirt size table, they are in the correct order, but on the form, it lists them in alpha. order....

2XL
3XL
Large
Medium
Small
XL

It's pretty frustrating to fill out the form because of this. Any way to sort them correctly without changing their values to 01Small 02Medium, etc..??

Thanks for your help!

 

[ColinEssex]

In the shirt size table create a new field (a number field)

Like this

OrderNumb. . . . . . Shirtsize

1. . . . . . . . . . . . . Small
2. . . . . . . . . . . . . Medium
3. . . . . . . . . . . . . Large
etc

Then in the ComboBox have the OrderNumb field as a hidden field and sort on that. Your sizes will then be in the correct order

Col

 

[boblarson]

Ohhh... I made the order field, but I didn't realize I had to hide it. I will figure out how to do that. Thanks!

Look at Column Widths and set each column like 0";1";0"

That example will show the second column and hide the first and third.

 

[gomikem]

OK Thanks for helping!!

I'm having a bit more trouble though. I have cascading Combo Boxes.

The Shirt Size table is "ShirtSize" and the fields are "Size" and "Order"

The following is the code I have. I don't see where I would add the order field. (Sorry if I look like an idiot. This access project was put on me and I'm learning as I go)

Private Sub LoadShirtSize()

strShirtSize = ""
If Not cboStyle.Text = "" Then

cboSize.RowSource = ""
cboSize.Value = ""

Dim test As String
Dim SQLStmt As String
Dim rsShirtSize As New ADODB.Recordset

SQLStmt = ""

cboStyle.SetFocus
SQLStmt = "SELECT DISTINCT ShirtStyle, ShirtType, ShirtSize, Inventory FROM ShirtAvailability WHERE ShirtStyle = '" & cboStyle.Text & "' "

'cboType.SetFocus

SQLStmt = SQLStmt & "AND ShirtType = '" & strShirtType & "'"

rsShirtSize.Open SQLStmt, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'test = rsShirtSize.RowSource

cboSize.SetFocus

cboSize.RowSourceType = "Value List"


While Not rsShirtSize.EOF
cboSize.AddItem (rsShirtSize![ShirtSize])
rsShirtSize.MoveNext
Wend 'Loop Until rsShirtSize.EOF

End If



End Sub

 

[boblarson]

SQLStmt = "SELECT DISTINCT ShirtStyle, ShirtType, ShirtSize, Inventory, SortOderFieldNameHere FROM ShirtAvailability WHERE ShirtStyle = '" & cboStyle.Text & "' "

 

[gomikem]

I've added the order field to ShirtAvailability and added "Order" to the SQL Statement, but the size drop down is still out of order.

Am I missing something?

 

[WayneRyan]

Mike,

SQLStmt = "SELECT DISTINCT ShirtStyle, ShirtType, ShirtSize, Inventory " & _
          "FROM ShirtAvailability " & _
          "WHERE ShirtStyle = '" & cboStyle.Text & "' " & _
          "Order By SortOderFieldNameHere"

Wayne

 

[gomikem]

I tried the following:

cboStyle.SetFocus
SQLStmt = "SELECT DISTINCT ShirtStyle, ShirtType, ShirtSize, Inventory, ShirtOrder " & _
"FROM ShirtAvailability " & _
"WHERE ShirtStyle = '" & cboStyle.Text & "' " & _
"ORDER BY ShirtOrder"

It gives this error:

Syntax Error (Missing Operator) in query expression 'ShirtOrder AND ShirtType= "".

Then the debug takes me to this line of code:

rsShirtSize.Open SQLStmt, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Anyone know what I'm doing wrong?

 

[WayneRyan]

Mike,

Your posted SQL only has

"WHERE ShirtStyle = '" & cboStyle.Text & "' " & _

Where does the 'ShirtOrder AND ShirtType= "" come from?

What was in cboStyle?

Also, you don't need the .Text, it's only valid in the OnChange event.
Use .Value or omit it entirely.

Can you post the DB?

Wayne

 

[gomikem]

Wayne,

Can I post attachments to PMs?

 

[WayneRyan]

Mike,

You can't attach to PMs.

Attach it to this thread, you'll get more help that way.

Wayne