First off I'm going to give my code then explain my problem....

Option Compare Database Option Explicit Private Sub cmdCompile_Click() Dim vntSta As Variant Dim vntBld As Variant Dim vntLst As Variant Dim strState As String, strBld As String, strLst As String, strSQL As String Dim dbs As Database Dim qdf As QueryDef          For Each vntLst In Me!titleList.ItemsSelected         strLst = strLst + """" & _         Me![titleList].ItemData(vntLst) & """, "     Next          For Each vntSta In Me!lstState.ItemsSelected         strState = strState + """" & _         Me![lstState].ItemData(vntSta) & """, "     Next          For Each vntBld In Me!bldLst.ItemsSelected         strBld = strBld + """" & _         Me![bldLst].ItemData(vntBld) & ""","              Next      strLst = Left$(strLst, Len(strLst)) strState = Left$(strState, Len(strState) - 2) strBld = Left$(strBld, Len(strBld) - 1) strLst = "(" + strLst + ")" strState = "(" + strState + ")" strBld = "(" + strBld + ")" If strState & strBld & strLst <> "" Then     Set dbs = CurrentDb     strSQL = "SELECT name.* INTO results FROM NAME WHERE TITLE IN " + strLst + " AND STATE IN " + strState + " AND BLDTYPE IN " + strBld + ""     Set qdf = dbs.CreateQueryDef("Compiled", strSQL)         Else             MsgBox "No data has been selected!", vbOKOnly     End If End Sub

The problem I have is that when someone selects something from the titleList (a list box on my form) it returns only the first item in the list. Even if you select the last title it will return only the first title in the list.

Me & Brick walls -  

I can't really see a problem, I tried the following code and that worked ok.

Option Compare Database Option Explicit Private Sub Command2_Click()     Dim x As Variant     For Each x In List0.ItemsSelected              MsgBox List0.ItemData(x)     Next      End Sub

I am using access 2000. How large is the db, if its now too big you can email it to me.

I figured it out... Talk about random luck at that.

By changing...

strLst = Left$(strLst, Len(strLst))

to

strLst = Left(strLst, Len(strLst))

It allowed for Long data and reports correct.

Strange I thought they were the same

I came back to this post to better help with Stadifers next post, thought I'd toss in my 2 cents...


Any of the Functions that use the optional "$" in the function use it so the developer can designate it as a String...this would supposedly make the code run faster.

i.e., Left, Right, Mid, UCase, etc. string counterparts are Left$, Right$ and Mid$, UCase$ (although I dont know how you would use U/LCase for anything other than string)...and just for fun try to get VB to accept "Len$(...)"

____________________________
Eggheads unite! You have nothing to lose but your yolks.