concanenation strings to pass to query (1 Viewer)

[ppoindexter]

i am using a form with 2 list boxes to pass selected values to a query/report
this code works if i select only 1 item from each list box, when i select different combinations of values (for example 2 items from LBox_Grades and 1 from LBox_Standards or varous other combinations) the report opens with the wrong records..also it doesnt handle null values (report will not open if either list box has no items selectioned)

any ideas?

Private Sub Command8_Click()

Dim varItem As Variant
Dim strWhere As String
Dim strWhereGrades As String
Dim strWhereStandards As String
Dim stDocName As String

If IsNull(Me.Combo_Report) = True Then
MsgBox ("Please select a Report")
Else


For Each varItem In Me![LBox_Grades].ItemsSelected
strWhereGrades = strWhereGrades & "fldlevel2id =" _
& Me![LBox_Grades].Column(0, varItem) & " Or "
Next varItem

strWhereGrades = Left(strWhereGrades, Len(strWhereGrades) - 4)

For Each varItem In Me![LBox_Standards].ItemsSelected
strWhereStandards = strWhereStandards & "fldlevel1id =" _
& Me![LBox_Standards].Column(0, varItem) & " Or "
Next varItem

strWhereStandards = Left(strWhereStandards, Len(strWhereStandards) - 4)


strWhere = strWhereGrades & " And " & strWhereStandards


stDocName = Me![Combo_Report].Column(2)

DoCmd.OpenReport stDocName, acPreview, , strWhere
end sub

 

[Kevin_S]

for multiple items in multi-select listboxes you have to use the In() function...

 

[ppoindexter]

thanks for responding
could you please explain a bit further ..

 

[Kevin_S]

OK - this is a pretty involved process so rather then trying to explain in detail I whipped up a demo and posted it here: Multi-Select Listbox and In() Function Have a look at this and let me know if you need help implementing this in your app...

HTH,
Kev

 

[ppoindexter]

hi kevin
the example you posted only uses one listbox

my form has 3 listboxes that users have the option of making selections from

can the example you posted be modified to accomadate 3 listboxes?
thank you

 

[Kevin_S]

just use the example and modify it with 3 listboxes... the principle is the same for 3, 4, 20 listboxes... all you have to do is copy what is done in the example for 1 and make it 3...

Kev

 

[ppoindexter]

ok
i was hoping that was the case
thanks so much

 

[Kevin_S]

no problem - if you still have trouble setting it up after you start trying to modify post back and I'll help you out...

Kevin