Sort Multi-select lbx (1 Viewer)

[steve1111]

Hello,

I have been looking for a solution and haven't found quite what i need. I have a multi select lbx that filters another multi select lbx. the SQL that i wrote work great up the the point when i am trying to sort by one of the columns in the lbx. Once i add the ORDER BY statement the lbx goes blank, other wise without it works great but the records are out of order.

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSortOrder As String



Set ctl = Forms!sysfReports!lbxDivision
strSQL = "SELECT * FROM dbo_Zones WHERE DivisionID="
strSortOrder = "ORDER BY dbo_Zones.Note;"


For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [DivisionID]="
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 17)

Me.lbxZone.RowSource = strSQL
Me.lbxZone.RowSource = Me.lbxZone.RowSource & " ORDER BY " & strSortOrder

Me.lbxZone.Requery

 

[Minty]

You are adding Order By twice...

 

[steve1111]

Thanks, but making the change still produces a blank lbx

Me.lbxZone.RowSource = strSQL
Me.lbxZone.RowSource = Me.lbxZone.RowSource & strSortOrder

 

[Minty]

Your Order By I think should only be " ORDER BY Note;"

I would also do it this way;

strSQL = StrSQL & StrSortOrder

Debug. Print strSQL
Me.lbxZone.RowSource = strSQL

Then you can see in the immediate window what your are actually passing in as a row source. If it looks right copy and paste it into a new query window and see the results.

 

[steve1111]

Thanks Minty, very nice!

 

[Minty]

You are welcome !