Search Based on Multi Selection in List Box

[Guest]

Hi All,

I know what is wrong with my code but I don't know how to fix it. It's
better to explain what is wrong with it in an example than to try to explain
it.

If a user wants to see all the phone calls for Division1 with a priority of
1 or 2 the results that are given are calls for Division1 with a priority of
1 and ALL the divisions with a priority of 2.

This is the Where string shown in my Debug Window:
([BUS Unit/Div] = "Division1") and ([Priority] = 1) or ([Priority] = 2)

This is my code for the list box:
With Me.lstPriority
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & " ([Priority] = " & .ItemData(varItem) & ") or "
End If
Next
End With

Any suggestions on how to fix this?

Thank you,
Linda

 

[John Vinson]

Hi All,

I know what is wrong with my code but I don't know how to fix it. It's
better to explain what is wrong with it in an example than to try to explain
it.

If a user wants to see all the phone calls for Division1 with a priority of
1 or 2 the results that are given are calls for Division1 with a priority of
1 and ALL the divisions with a priority of 2.

This is the Where string shown in my Debug Window:
([BUS Unit/Div] = "Division1") and ([Priority] = 1) or ([Priority] = 2)

This is my code for the list box:
With Me.lstPriority
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & " ([Priority] = " & .ItemData(varItem) & ") or "
End If
Next
End With

Any suggestions on how to fix this?

Add an extra parenthesis. The Where string should be

([BUS Unit/Div] = "Division1") and (([Priority] = 1) or ([Priority]
= 2))

Note that the entire list of OR's is within parentheses, so it gets
evaluated as a whole and then AND'd with the Unit/Div criterion.

Alternatively, and perhaps more efficiently, use the IN() clause:

strWhere = "([BUS Unit/Div] = 'Division1') and "
(or however you built this part of the query)

strWhere = strWhere & " AND [Priority] IN ("
With Me.lstPriority
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & .ItemData(varItem) & ", "
End If
Next
End With
' trim off trailing comma
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

This should build

([BUS Unit/Div] = 'Division1') and [Priority] IN (1, 2, 3)

John W. Vinson[MVP]
John W. Vinson[MVP]

 

[Guest]

Will this work if the user does not choose a Priority? If I understand this
correctly, your IN clause example suggests the program will expect a
selection in the Priority list box. Is this correct?

Hi All,

I know what is wrong with my code but I don't know how to fix it. It's
better to explain what is wrong with it in an example than to try to explain
it.

If a user wants to see all the phone calls for Division1 with a priority of
1 or 2 the results that are given are calls for Division1 with a priority of
1 and ALL the divisions with a priority of 2.

This is the Where string shown in my Debug Window:
([BUS Unit/Div] = "Division1") and ([Priority] = 1) or ([Priority] = 2)

This is my code for the list box:
With Me.lstPriority
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & " ([Priority] = " & .ItemData(varItem) & ") or "
End If
Next
End With

Any suggestions on how to fix this?

Add an extra parenthesis. The Where string should be

([BUS Unit/Div] = "Division1") and (([Priority] = 1) or ([Priority]
= 2))

Note that the entire list of OR's is within parentheses, so it gets
evaluated as a whole and then AND'd with the Unit/Div criterion.

Alternatively, and perhaps more efficiently, use the IN() clause:

strWhere = "([BUS Unit/Div] = 'Division1') and "
(or however you built this part of the query)

strWhere = strWhere & " AND [Priority] IN ("
With Me.lstPriority
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & .ItemData(varItem) & ", "
End If
Next
End With
' trim off trailing comma
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"

This should build

([BUS Unit/Div] = 'Division1') and [Priority] IN (1, 2, 3)

John W. Vinson[MVP]
John W. Vinson[MVP]

 

[John Vinson]

Will this work if the user does not choose a Priority? If I understand this
correctly, your IN clause example suggests the program will expect a
selection in the Priority list box. Is this correct?

That's correct. What do you want to happen if nothing is selected in
the listbox?

If you want to return no records (which makes sense to me, but I don't
know your data) you could "seed" the IN() clause with a Priority which
does not exist:

strWhere = strWhere & " AND [Priority] IN (0, "

Then if the user selects nothing, the criterion will end up

[Priority] IN (0)

which is a valid SQL clause, but will not return any records (unless
of course there is a priority 0 record in the table...!)

John W. Vinson[MVP]