Multi-Select List box as query parameter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Judy Ward - 29 Jul 2005 02:37 GMT

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

Reply to this Message

Allen Browne - 29 Jul 2005 03:31 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 25 lines]
> Thank you,
> Judy

Reply to this Message

Judy Ward - 30 Jul 2005 01:23 GMT

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.
[quoted text clipped - 33 lines]
> > Thank you,
> > Judy

Reply to this Message

Allen Browne - 30 Jul 2005 11:20 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 52 lines]
>> > Thank you,
>> > Judy

Reply to this Message

John Griffiths - 31 Jul 2005 00:00 GMT

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

> I have a parameterized query (qrySAR_Status_by_IR) that I am passing a string
> to through a form:
[quoted text clipped - 22 lines]
> Thank you,
> Judy

Reply to this Message