Multi-Select List box as query parameter

[Guest]

I have a parameterized query (qrySAR_Status_by_IR) that I am passing a string
to through a form:
SELECT tblSAR.SAR_ID, tblSAR.SAR_Multipurpose
FROM tblSAR
WHERE (((tblSAR.SAR_Multipurpose) Like
[Forms]![frmMain]![txtBuildParameter]));

I am building the string through a multi-select list box on the form:
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "*" & ctl.ItemData(varItem) & "*" _
& " OR (tblSAR.SAR_Multipurpose) Like "
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, (Len(strSQL) - 35))
Me.txtBuildParameter = strSQL
DoCmd.OpenQuery "qrySAR_Status_by_IR"

This code works as expected with one selection, but returns nothing when two
or more items are selected. A Debug.Print on strSQL returns:
*IR-7* ' For one selection--and this works
*IR-7* OR (tblSAR.SAR_Multipurpose) Like *IR-8* 'For two selections--which
does not work

Any idea what I am doing wrong?
Thank you,
Judy

 

[Allen Browne]

The Expression Service cannot parse the items from a multi-select list box
and use them as criteria in a query.

You can create the string programmatically by looping through the
ItemsSelected collection of the list box, and concatenting them into a SQL
string. For an example of how to build the WHERE clause in code, see:
http://allenbrowne.com/ser-50.html

 

[Guest]

What I am trying to do came from:
http://www.mvps.org/access/forms/frm0007.htm

The only difference I can see is that this example uses numbers and I am
using strings. I have tried every combination of syntax I can think of to
get this to work (are you sure it isn't just a syntax issue?).

I did look at your example, but you are calling a report (not a query), and
that introduces another level of difficulty for me. I guess I will have to
try that next if there is no way to make my current code work.

Thank you for responding,
Judy

The Expression Service cannot parse the items from a multi-select list box
and use them as criteria in a query.

You can create the string programmatically by looping through the
ItemsSelected collection of the list box, and concatenting them into a SQL
string. For an example of how to build the WHERE clause in code, see:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a parameterized query (qrySAR_Status_by_IR) that I am passing a
string
to through a form:
SELECT tblSAR.SAR_ID, tblSAR.SAR_Multipurpose
FROM tblSAR
WHERE (((tblSAR.SAR_Multipurpose) Like
[Forms]![frmMain]![txtBuildParameter]));

I am building the string through a multi-select list box on the form:
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "*" & ctl.ItemData(varItem) & "*" _
& " OR (tblSAR.SAR_Multipurpose) Like "
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, (Len(strSQL) - 35))
Me.txtBuildParameter = strSQL
DoCmd.OpenQuery "qrySAR_Status_by_IR"

This code works as expected with one selection, but returns nothing when
two
or more items are selected. A Debug.Print on strSQL returns:
*IR-7* ' For one selection--and this works
*IR-7* OR (tblSAR.SAR_Multipurpose) Like *IR-8* 'For two
selections--which
does not work

Any idea what I am doing wrong?
Thank you,
Judy

 

[Allen Browne]

If this is a Text field, (not a Number field), you need to add quotes as a
delimiter around each value.

Using the code from the mvps.org article, you need this line inside the
loop:
strSQL = strSQL & """" & ctl.ItemData(varItem) & """ OR [EmpID]="

To help you debug it, at the end of the procedure add:
Debug.Print strSQL
The open the Immediate window (Ctrl+G) and copy the statement to clipboard.

Create a new query, no table.
Switch to SQL View (View menu.)
Paste.
Test the query. You can switch back to query design view to see how Access
understands it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

What I am trying to do came from:
http://www.mvps.org/access/forms/frm0007.htm

The only difference I can see is that this example uses numbers and I am
using strings. I have tried every combination of syntax I can think of to
get this to work (are you sure it isn't just a syntax issue?).

I did look at your example, but you are calling a report (not a query),
and
that introduces another level of difficulty for me. I guess I will have
to
try that next if there is no way to make my current code work.

Thank you for responding,
Judy

The Expression Service cannot parse the items from a multi-select list
box
and use them as criteria in a query.

You can create the string programmatically by looping through the
ItemsSelected collection of the list box, and concatenting them into a
SQL
string. For an example of how to build the WHERE clause in code, see:
http://allenbrowne.com/ser-50.html

I have a parameterized query (qrySAR_Status_by_IR) that I am passing a
string
to through a form:
SELECT tblSAR.SAR_ID, tblSAR.SAR_Multipurpose
FROM tblSAR
WHERE (((tblSAR.SAR_Multipurpose) Like
[Forms]![frmMain]![txtBuildParameter]));

I am building the string through a multi-select list box on the form:
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "*" & ctl.ItemData(varItem) & "*" _
& " OR (tblSAR.SAR_Multipurpose) Like "
Next varItem
'Trim the end of strSQL
strSQL = Left$(strSQL, (Len(strSQL) - 35))
Me.txtBuildParameter = strSQL
DoCmd.OpenQuery "qrySAR_Status_by_IR"

This code works as expected with one selection, but returns nothing
when
two
or more items are selected. A Debug.Print on strSQL returns:
*IR-7* ' For one selection--and this works
*IR-7* OR (tblSAR.SAR_Multipurpose) Like *IR-8* 'For two
selections--which
does not work

Any idea what I am doing wrong?
Thank you,
Judy

 

[John Griffiths]

Dim strSQL As String
strSQL = "SELECT tblSAR.SAR_ID, tblSAR.SAR_Multipurpose " & _
"FROM tblSAR "

Dim strWhere As String
strWhere = ""

For Each varItem In ctl.ItemsSelected
If strWhere = "" Then
strWhere = " tblSAR.SAR_Multipurpose LIKE '*" &
ctl.ItemData(varItem) & "*'" & vbNewLine
Else
strWhere = strWhere & " OR tblSAR.SAR_Multipurpose LIKE '*" &
ctl.ItemData(varItem) & "*'" & vbNewLine
End If
Next varItem

If strWhere <> "" Then
strSQL = strSQL & " WHERE " & vbNewLine & strWhere
End If

Dim qry As New DAO.QueryDef
qry.SQL = strSQL
qry.Name = "YourQueryName_" & CurrentUser() & "+" & Format(Now(),
"yyyymmddhhnnss")
DAO.Workspaces(0).Databases(0).QueryDefs.Append qry

DoCmd.OpenQuery qry.Name

On the right lines? - John