Var for Multiple Select ListBox values in query criteria

  • Thread starter Thread starter David McDonald
  • Start date Start date
D

David McDonald

I am using a listbox with multiple selections enabled. I
have created a function that successfully returns the
value of a variable that is loaded from the selections in
the listbox.
I use this function as selection criteria in a query.

Problem is that multiple selections return Null results
in the query; although single selection returns correct
data, and no selections returns all records (which is
correct).
If I cut and paste the result of the function from the
immediate window into the query, the query works fine.
For some reason, when the function is in the query and
multiple selections are made, it does not work!

I have tried variation with adding Chr(34) (double
quotes) as well as single quotes. The word 'Or' gets
inserted, it looks good, it just doesnt work.

HELP PLEASE - Code below:

Public SeriesSelected As String

Private Sub List8_LostFocus()
Dim ctlSource As Object
Dim IntCurrentRow As Integer
Set ctlSource = Forms!Frm_Testing!List8
SeriesSelected = ""
For IntCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(IntCurrentRow) Then
'SeriesSelected = SeriesSelected & Chr(39) & Trim
(Str(ctlSource.Column(0, IntCurrentRow))) & Chr(39) & "
Or "
'SeriesSelected = SeriesSelected & Chr(34) & Trim
(Str(ctlSource.Column(0, IntCurrentRow))) & Chr(34) & "
Or "
SeriesSelected = SeriesSelected & Trim(Str
(ctlSource.Column(0, IntCurrentRow))) & " Or "
'SeriesSelected = "'" & SeriesSelected &
ctlSource.Column(0, IntCurrentRow) & "'" & " Or "
End If
Next IntCurrentRow
If Len(SeriesSelected) > 4 Then
SeriesSelected = Left(SeriesSelected, Len
(SeriesSelected) - 4)
'SeriesSelected = "Like " & SeriesSelected
Else
'SeriesSelected = Chr(34) & "*" & Chr(34)
'SeriesSelected = "Like " & Chr(34) & "*" & Chr(34)
SeriesSelected = "*"
End If

Public Function Return_SeriesSelected()
If SeriesSelected = "" Then
Return_SeriesSelected = "*"
Else
Return_SeriesSelected = SeriesSelected
End If
End Function
 
David McDonald said:
I am using a listbox with multiple selections enabled. I
have created a function that successfully returns the
value of a variable that is loaded from the selections in
the listbox.
I use this function as selection criteria in a query.

Problem is that multiple selections return Null results
in the query; although single selection returns correct
data, and no selections returns all records (which is
correct).
If I cut and paste the result of the function from the
immediate window into the query, the query works fine.
For some reason, when the function is in the query and
multiple selections are made, it does not work!

I have tried variation with adding Chr(34) (double
quotes) as well as single quotes. The word 'Or' gets
inserted, it looks good, it just doesnt work.

HELP PLEASE - Code below:

Public SeriesSelected As String

Private Sub List8_LostFocus()
Dim ctlSource As Object
Dim IntCurrentRow As Integer
Set ctlSource = Forms!Frm_Testing!List8
SeriesSelected = ""
For IntCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(IntCurrentRow) Then
'SeriesSelected = SeriesSelected & Chr(39) & Trim
(Str(ctlSource.Column(0, IntCurrentRow))) & Chr(39) & "
Or "
'SeriesSelected = SeriesSelected & Chr(34) & Trim
(Str(ctlSource.Column(0, IntCurrentRow))) & Chr(34) & "
Or "
SeriesSelected = SeriesSelected & Trim(Str
(ctlSource.Column(0, IntCurrentRow))) & " Or "
'SeriesSelected = "'" & SeriesSelected &
ctlSource.Column(0, IntCurrentRow) & "'" & " Or "
End If
Next IntCurrentRow
If Len(SeriesSelected) > 4 Then
SeriesSelected = Left(SeriesSelected, Len
(SeriesSelected) - 4)
'SeriesSelected = "Like " & SeriesSelected
Else
'SeriesSelected = Chr(34) & "*" & Chr(34)
'SeriesSelected = "Like " & Chr(34) & "*" & Chr(34)
SeriesSelected = "*"
End If

Public Function Return_SeriesSelected()
If SeriesSelected = "" Then
Return_SeriesSelected = "*"
Else
Return_SeriesSelected = SeriesSelected
End If
End Function
Click to expand...

You simply can't do it that way, I'm afraid. The problem is that the
function returns a string, and the query compares the field in the table
to that string. If the string contains a single value, then the
comparison may work the way you want, but if the string contains
multiple values connected by " OR ", then the query is going to try to
compare the field value with the string *including the values and the OR
conjunctions*, and will never find a match. It's like writing this in
code:

If "A" = "A OR B" Then ...

The value "A" will never be equal to the value "A OR B".

This sort of thing is best done by writing the whole SQL statement for
the query in code. Then you can take a statement stub like

strSQL = "SELECT * FROM MyTable WHERE MyField "
and a constructed list of values like this:

strList = "'A', 'B', 'C'"

(which would normally be built by looping through the list box), and put
them together to make a SQL statement

strSQL = strSQL & "IN (" & strList & ")"

and then find a way to use that constructed SQL statement as the
RecordSource for a form or report:

DoCmd.OpenForm "frmShowResults"
Form!frmShowResults.RecordSource = strSQL

There are lots of variations of this, but do you get the idea?
 
Back
Top