Multi-Select list box as query criteria

R

ReportSmith

I've read all of the other posts and they've been very helpful, but I'm still
stuck.

I have a form frmMain, a list box (lstParticipant) with Multi Select set to
"Extended", and a text box (hidden) that contains my select statement:
......
Set frm = Forms!frmMain
Set ctl = frm!lstParticipant
strSQL = "SELECT [Participant] FROM [qryABC] WHERE [Participant]= " & """"

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & """" & " OR [Participant]=
" & """"
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 20)
txtParameter = Trim(strSQL) 'I know this is extra
......
in addition, I have the following code to be able to pass this sql string to
the query...
......
Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub
......
Within my query, I have the field [Participant] with the citeria grid entry
of [forms]![frmMain]![txtParameter]

For a single selection, I can get it to work, for multiple - no can do.
Another question is: should I be looking at the query in "SELECT
[Participant] FROM [qryABC] WHERE [Participant]= " & """" (it seems to be a
recursive call) or change [qryABC] to the original table name?

I've changed the code so many times that it may not be exactly what will
work for the single criterion, but any help would be greatly appreciated.
 
K

Ken Snell MVP

You cannot pass an SQL statement as a parameter to a query.

Your code is confusing to me. The following code step updates a control with
a value after you manually update that same control?

Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub

And the above code is good only for a single-select listbox. It will not
work for a multi-select listbox.

What are you wanting to do? Filter a query for a report, using the
Participant filters? Or just change the SQL statement of a stored query?

Perhaps my sample database will give you some ideas for what you want to do:

Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm
 
K

Ken Snell MVP

You cannot pass an SQL statement as a parameter to a query.

Your code is confusing to me. The following code step updates a control with
a value after you manually update that same control?

Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub

And the above code is good only for a single-select listbox. It will not
work for a multi-select listbox.

What are you wanting to do? Filter a query for a report, using the
Participant filters? Or just change the SQL statement of a stored query?

Perhaps my sample database will give you some ideas for what you want to do:

Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm
 
R

ReportSmith

Thanks Ken for the reply - I'll look at the example you listed.
As far as your question...I want to filter a query for dataset that I will
either use for a report or simply export, using the Participant filters.
 
R

ReportSmith

Thanks Ken for the reply - I'll look at the example you listed.
As far as your question...I want to filter a query for dataset that I will
either use for a report or simply export, using the Participant filters.
 
R

ReportSmith

To save time (I've been working on this far too much - using it as a lesson
to myself and as an actual project), I changed the list box to a
(single-select) combo box and have an append query write each selection to a
table that I will then export.

It works and has some intrinsic error handling as well (not bad).

Thanks again for your help.

ReportSmith said:
Thanks Ken for the reply - I'll look at the example you listed.
As far as your question...I want to filter a query for dataset that I will
either use for a report or simply export, using the Participant filters.

ReportSmith said:
I've read all of the other posts and they've been very helpful, but I'm still
stuck.

I have a form frmMain, a list box (lstParticipant) with Multi Select set to
"Extended", and a text box (hidden) that contains my select statement:
.....
Set frm = Forms!frmMain
Set ctl = frm!lstParticipant
strSQL = "SELECT [Participant] FROM [qryABC] WHERE [Participant]= " & """"

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & """" & " OR [Participant]=
" & """"
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 20)
txtParameter = Trim(strSQL) 'I know this is extra
.....
in addition, I have the following code to be able to pass this sql string to
the query...
.....
Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub
.....
Within my query, I have the field [Participant] with the citeria grid entry
of [forms]![frmMain]![txtParameter]

For a single selection, I can get it to work, for multiple - no can do.
Another question is: should I be looking at the query in "SELECT
[Participant] FROM [qryABC] WHERE [Participant]= " & """" (it seems to be a
recursive call) or change [qryABC] to the original table name?

I've changed the code so many times that it may not be exactly what will
work for the single criterion, but any help would be greatly appreciated.
Click to expand...
Click to expand...
 
R

ReportSmith

To save time (I've been working on this far too much - using it as a lesson
to myself and as an actual project), I changed the list box to a
(single-select) combo box and have an append query write each selection to a
table that I will then export.

It works and has some intrinsic error handling as well (not bad).

Thanks again for your help.

ReportSmith said:
Thanks Ken for the reply - I'll look at the example you listed.
As far as your question...I want to filter a query for dataset that I will
either use for a report or simply export, using the Participant filters.

ReportSmith said:
I've read all of the other posts and they've been very helpful, but I'm still
stuck.

I have a form frmMain, a list box (lstParticipant) with Multi Select set to
"Extended", and a text box (hidden) that contains my select statement:
.....
Set frm = Forms!frmMain
Set ctl = frm!lstParticipant
strSQL = "SELECT [Participant] FROM [qryABC] WHERE [Participant]= " & """"

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & """" & " OR [Participant]=
" & """"
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 20)
txtParameter = Trim(strSQL) 'I know this is extra
.....
in addition, I have the following code to be able to pass this sql string to
the query...
.....
Private Sub txtParameter_AfterUpdate()
Me.txtParameter = lstParticipant.Column(0)
End Sub
.....
Within my query, I have the field [Participant] with the citeria grid entry
of [forms]![frmMain]![txtParameter]

For a single selection, I can get it to work, for multiple - no can do.
Another question is: should I be looking at the query in "SELECT
[Participant] FROM [qryABC] WHERE [Participant]= " & """" (it seems to be a
recursive call) or change [qryABC] to the original table name?

I've changed the code so many times that it may not be exactly what will
work for the single criterion, but any help would be greatly appreciated.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi-Select List Box as Query Parameter 1
multi select list box in a form as a query parameter 3
List Box to Filter Report 5
Error in multiple select in a list box code 4
Problem with multiple selection list box 4
multi-select list box in sql statement issue 3
Multiselect listbox as criteria 6
Multi-Select List Box 2

Top