Solved: Is it possible to "ORDER BY IN" ?

[Dr.K]

The app I'm working on uses two different DBs: one contains lists of Security IDs, and the other DB contains the TickerSymbols, ShortNames and LongNames that corrospond to the IDs.

Right now, my code gets a list of IDs from DBA (RecordesetA), and then gets a recordset from DBB that contains all of the info for those securites, using a WHERE IN list to pull only the relevant data. The problem is that I have no way of sorting the records: what would be ideal is if the Recordset from DBB came back in the exact same order as the IN list.

My workaround is to just use FIND to pull the correct data from RecordsetB. Works perfectly, but the code would be a simpler if I could just order RecordsetB by the list used to create it.

[vba] strSQL = "SELECT [tblLineUpItems].[Position], [tblLineUpItems].[SecID]" _
& " FROM [tblLineUpItems]" _
& " WHERE ((([tblLineUpItems].[Position]) <> 0)" _
& " And (([tblLineUpItems].[LineUpID]) = " & LineUpID & "))" _
& " ORDER BY [tblLineUpItems].[Position];"

rstAcc.Open strSQL, conAcc, adOpenStatic, adLockReadOnly

strList = "("
rstAcc.movefirst
Do
strList = strList & rstAcc!SecID
rstAcc.movenext
If rstAcc.EOF = False Then strList = strList & ", "
Loop While rstAcc.EOF = False
strList = strList & ")"

strSQL = "SELECT MSE_SEC_ID, MSE_TCKR_SYMB, MSE_SEC_SHT_NM, MSE_SEC_LNG_NM" _
& " FROM MSECURTY" _
& " WHERE MSE_SEC_ID IN " & strList

rstDM.Open strSQL, conDM, adOpenStatic, adLockReadOnly

With ufoBuild1.listLineUp
rstAcc.movefirst
Let x = 0
Do
rstDM.movefirst
rstDM.Find ("MSE_SEC_ID = " & rstAcc!SecID)
.AddItem
.List(x, 0) = Null2Empty(rstDM!MSE_TCKR_SYMB)
.List(x, 1) = rstDM!MSE_SEC_SHT_NM
.List(x, 2) = rstDM!MSE_SEC_LNG_NM
.List(x, 3) = rstDM!MSE_SEC_ID
x = x + 1
rstAcc.movenext
Loop Until rstAcc.EOF = True
End With
rstAcc.Close
rstDM.Close
[/vba]

[XLGibbs]

Why can't you just do this?

[VBA]strSQL = "SELECT MSE_SEC_ID, MSE_TCKR_SYMB, MSE_SEC_SHT_NM, MSE_SEC_LNG_NM" _
& " FROM MSECURTY" _
& " WHERE MSE_SEC_ID IN " & strList _
& " ORDER BY MSE_SEC_ID"
[/VBA]

[Dr.K]

I can't do that becuase the SecIDs are just random Longs, ordering by the SecID would be meaningless. The DM DB contains over 2000 SecIDs.

I need to sort the first set by Position, so that they are already in the correct order. If I ordered BOTH sets by SecID, then the Recordsets would line up, but I would have to do a Find on the Poition field in the rstAcc so that I could pull the data out in the correct order.

Whew... did that make any sense?

[carlmack]

Not sure what your keys are but maybe

SELECT     MSE_SEC_ID, MSE_TCKR_SYMB, MSE_SEC_SHT_NM, MSE_SEC_LNG_NM
FROM         MSECURTY INNER JOIN
                     tblLineUpItems ON MSECURTY.MSE_SEC_ID = tblLineUpItems.SecID
ORDER BY tblLineUpItems.Position

?

[Dr.K]

LOL, good luck trying to join a table from a DB2 DB to a table in an Access DB. This is the crux of the problem, I'm pulling from two completely separate DBs.

I guess this is impossible, but since my current code works, I'll just mark this as Solved and move on.

Thanks for all the input, guys.

[carlmack]

Ha - OK. Two Access dbs maybe but I just looked up DB2 it says released in 1983 !