The dreaded where clause - second list box 2

[pdldavis]

Hi, I have two simple select list boxes , lstType and lstPriority I am using as part of the Where Clause.

I can pick up all the itemsSelected from lstType but can only pick up the first item selected from LstPriority. I believe I need to do another For-Next with the second list box using VarItemP but don't know how to include it.


Dim varItem As Variant
Dim VaritemP As Variant

If (lstType.ItemsSelected.Count = 0) Then Exit Sub
For Each varItem In lstType.ItemsSelected
StrsqlWhere = "Where " & "([tblSystem].[Sys]) = '" & (lstType.Column(0, varItem)) & "' and " & _
"[tblListType].[priority] = '" & (lstPriority.Column(0, VaritemP)) & "' OR "

Next varItem

Any help would be appreciated.

Thanks, Dan

 

[FancyPrairie]

Check out this FAQ faq181-5497. It contains a function that will build the Where clause for you. You only have to do 3 things to make it work.

1. Create a new module, highlight and copy the functions from the FAQ and paste them in the new module. Name the new module something like basBuildWhere.

2. Set your tag properties of your list boxes as described in the FAQ (see headers of modules). It is very important that you get the tag properties correct, because they are the key to making it work.

3. Open your report as indicated in the FAQ (i.e. Docmd.OpenReport "YourReport",,,BuildWhere(Me)

I would suggest getting just 1 list box to work at first. Once you have the tag property of the list box defined, place a command button on your form and in the OnClick event of the command button, enter this:

MsgBox BuildWhere Me

This way you can see what BuildWhere is returning to verify if it is correct.

 

[PHV]

If lstPriority.ItemsSelected.Count = 0 _
Or lstType.ItemsSelected.Count = 0 Then Exit Sub
StrsqlWhere = "WHERE tblSystem.Sys In ("
For Each varItem In lstType.ItemsSelected
StrsqlWhere = StrsqlWhere & "'" & lstType.Column(0, varItem) & "',"
Next
StrsqlWhere = Left(StrsqlWhere, Len(StrsqlWhere) - 1) & ") AND tblListType.priority In ("
For Each varItem In lstPriority.ItemsSelected
StrsqlWhere = StrsqlWhere & "'" & lstPriority.Column(0, varItem) & "',"
Next
StrsqlWhere = Left(StrsqlWhere, Len(StrsqlWhere) - 1) & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886