Multi List (honest I've already searched)

[Lyncroft]

I'm using this code to pick out items on a list box and create a query. I've only put this bit of code because I think this is where the problem is (hopefully)


For Each varItem In Me.List0.ItemsSelected
sPerson = sPerson & Me.List0.ItemData(varItem)
Next varItem
sPerson = " [ID] in (" & sPerson & ")"

'SQL criteria
sCriteria = sPerson

This works fine if I choose just one item on the list. However, If I choose 3 items it doesn't show 3 records it just shows the one record. IE, if I choose ID 3,4, 6 it shows the single record for ID 346.

Any ideas gratefully received.

 

[Lyncroft]

Mucked around with this a bit more and it works!



For Each varItem In Me.List0.ItemsSelected
sPerson = sPerson & "," & Me.List0.ItemData(varItem)
Next varItem
sPerson = Mid(sPerson, 2) ' remove leading comma.
sPerson = " [ID] in (" & sPerson & ")"

 

[aprilsong]

Multi-choice List box

I've been working on a similar problem yesterday & today. Like you I also researched...

Thanks for your messages. I'm still having trouble, however, in getting my report to pull more than one entry from my list box (a list of states). Can you help? Here's a sample of my code:

'Show the list of state criteria
With Me.State
For Each varItem In .ItemsSelected
strCrit = strCrit & "," & .Column(0, varItem)
Next varItem
CritList = Mid(strCrit, 2) ' remove leading comma.
End With

'print report
Dim strCriteria

strCriteria = " [Process Days test]![State] in (" & CritList & ")"

DoCmd.OpenReport "Effective Dates + Increase Detail", acPreview, "", "(Forms![Increase History Multiple Dialog Box]![Plan1] = [Process Days test]![Plan]) And (Forms![Increase History Multiple Dialog Box]![MktgGroup] = [Process Days test]![Group]) And (Forms![Increase History Multiple Dialog Box]![Co] = [Process Days test]![Company]) AND ([Process Days test]![State] in (Forms![Increase History Multiple Dialog Box]![CritList]))"

Thanks!

 

[aprilsong]

Multi-choice list box - cont

I also tried it this way...

DoCmd.OpenReport "Effective Dates + Increase Detail", acPreview, "", "(Forms![Increase History Multiple Dialog Box]![Plan1] = [Process Days test]![Plan]) And (Forms![Increase History Multiple Dialog Box]![MktgGroup] = [Process Days test]![Group]) And (Forms![Increase History Multiple Dialog Box]![Co] = [Process Days test]![Company]) AND (strCriteria))"

Thanks again!