[SOLVED] Is it possible to order by a combo box value list?

Is it possible to order by a combo box value list?

I'm working with Access and I'm writing a SQL query where I would like to order by the combo box value list sequence. For example if the Combox List of Row Source type: Value List and Row source of : Monday,Tuesday,Wednesday,etc...

Would be ordered according to the sequence found in the combo box not by alphabetical order.

Solution

You can use an IIF to set the order.

ORDER BY IIf([FIELD]="Monday", 0, IIf([Status]="Tuesday", 1, 2))

So you could build the SQL in vba, get the values from the ComboBox and build this into your ORDER BY claus.

Say your combo is called cboDays

You can loop the combo and get the values.

Dim strOrderBy As String
Dim intCounter As Integer
For intCounter = 0 To cboDays.ListCount - 1
  Debug.Print cboDays.ItemData(intCounter)
  strOrderBy = strOrderBy & "IIf([FIELD]="& Chr(34) & cboDays.ItemData(intCounter) & Chr(34) & ", " & i & ", "
Next

'Remove the last comma
Dim intOBLen As Integer
intOBLen = Len(strOrderBy)
strOrderBy = Left(strOrderBy, intSSLen - 1)

strOrderBy = "ORDER BY " & strOrderBy & ")"

Then add this to the rest of your SQL and set the Form's RowSource to this SQL.