Running a parameter query based on multi select list box values

[BWD]

Hi Group,

I have been trying to get this to work for days now and am hoping that
someone may have some answers.

I have a form with 4 multi select list boxes; lstBox1, lstBox2,
lstBox3 and lstBox4. What I
would like to do is create a parameter query that utilizes the values
selected from each of the 4 list boxes.

To get started I created a text field on my form to hold the selected
values. The code behind the text box loops through the selected
values from listbox1 and sets the value of the text box equal to;
"Value1", "Value2" "Value3" etc.

My parameter query has the following SQL statement:

SELECT

.FieldName
FROM

WHERE ((

.FieldName) In (Forms!frmName.TextBox));

Although the text box value holds the correct values, the query
produces a blank result set (I know that the values are in the
tables).

I can hard code the query with these values but that defeats the
purpose of looping through the list boxes.

Has anyone done this or know the answer?

Thanks!

 

[John Spencer]

It fails because what gets passed to the query is a string
"Value1, Value2, Value3"
And the match then is on what is equal to "Value1, Value2, Value3"

To do what you want you need to build the SQL in VBA or you can try this
technique if your values are all text
SELECT

.FieldName
FROM

WHERE InStr (1, Forms!frmName!TextBox,

.[FieldName]) > 0

That method may be quite slow with large sets of data

You might take a look at the following as a start
Multi-select ListBox is a bit more complex. See The Access Web article:

http://www.mvps.org/access/forms/frm0007.htm

Also look at
This question gets asked every few weeks, so there is a new article at:
http://allenbrowne.com/ser-50.html
illustrating how to use an unbound list box to select multiple entries, and
open a report that is limited to just the selected entries.

The code works with any version from 95 onwards, but in the more recent
versions it prints an explanation of the filter on the report.

Allen Browne - Microsoft MVP. Perth, Western Australia.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[BWD]

It fails because what gets passed to the query is a string
"Value1, Value2, Value3"
And the match then is on what is equal to "Value1, Value2, Value3"

To do what you want you need to build the SQL in VBA or you can try this
technique if your values are all text
SELECT

.FieldName
FROM

WHERE InStr (1, Forms!frmName!TextBox,

.[FieldName]) > 0

That method may be quite slow with large sets of data

You might take a look at the following as a start
Multi-select ListBox is a bit more complex. See The Access Web article:

http://www.mvps.org/access/forms/frm0007.htm

Also look at
This question gets asked every few weeks, so there is a new article at:
http://allenbrowne.com/ser-50.html
illustrating how to use an unbound list box to select multiple entries, and
open a report that is limited to just the selected entries.

The code works with any version from 95 onwards, but in the more recent
versions it prints an explanation of the filter on the report.

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -http://allenbrowne.com/tips.html
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi Group,

I have been trying to get this to work for days now and am hoping that
someone may have some answers.

I have a form with 4 multi select list boxes; lstBox1, lstBox2,
lstBox3 and lstBox4. What I
would like to do is create a parameter query that utilizes the values
selected from each of the 4 list boxes.

To get started I created a text field on my form to hold the selected
values. The code behind the text box loops through the selected
values from listbox1 and sets the value of the text box equal to;
"Value1", "Value2" "Value3" etc.

My parameter query has the following SQL statement:

SELECT

.FieldName
FROM

WHERE ((

.FieldName) In (Forms!frmName.TextBox));

Although the text box value holds the correct values, the query
produces a blank result set (I know that the values are in the
tables).

I can hard code the query with these values but that defeats the
purpose of looping through the list boxes.

Has anyone done this or know the answer?

Thanks!- Hide quoted text -

- Show quoted text -

Thanks for the response.

I tried the Instr command with no luck.

Also, I have the SQL for this query written in VB as code for a
report. Is there a way to make that code run in a query that I can
view in datasheet format?

Thanks

 

[Dirk Goldgar]

Hi Group,

I have been trying to get this to work for days now and am hoping that
someone may have some answers.

I have a form with 4 multi select list boxes; lstBox1, lstBox2,
lstBox3 and lstBox4. What I
would like to do is create a parameter query that utilizes the values
selected from each of the 4 list boxes.

To get started I created a text field on my form to hold the selected
values. The code behind the text box loops through the selected
values from listbox1 and sets the value of the text box equal to;
"Value1", "Value2" "Value3" etc.

My parameter query has the following SQL statement:

SELECT

.FieldName
FROM

WHERE ((

.FieldName) In (Forms!frmName.TextBox));

Although the text box value holds the correct values, the query
produces a blank result set (I know that the values are in the
tables).

I can hard code the query with these values but that defeats the
purpose of looping through the list boxes.

Has anyone done this or know the answer?

That approach won't work, for reasons that John Spencer stated. And John
also showed you how to use InStr to search in the delimited list you created
in your text box.

Just for laughs, I wrote a function you could call from a query to check
directly in a list box to see if a value is selected in the list box. I'm
sure it will be relatively slow, but it might come in handy for queries of
small tables, or when the tradeoff between speed and simplicity tilts toward
simplicity. Here's the code:

'----- start of code -----
Function IsSelectedInListbox( _
pListboxRef As String, _
pValue As Variant) _
As Variant

' Accepts a string that can be interpreted as a reference to a list box,
' and a value. Returns True if the value is selected in the list box,
' False if not. Note that it's the bound column of the list box that is
' tested for the value, not necessarily the displayed column.
'
' Arguments:
'
' pListboxRef -
' This is a string of the form "FormName!ListboxName", or
' "FormName!SubformName!ListboxName",
' or "Formname!SubformName!SubSubFormName!ListboxName", etc.
' Note that subform names must be provided as the names of the
' subform *controls* on their parent forms, as in normal
' form/subform references.
'
' pValue -
' This is the value that will be sought in the selected items
' of the list box.
'
' Copyright © Dirk Goldgar, 2007
' Permission: You may use this function in your applications and
' distribute it freely, provided that the copyright notice
' remains unchanged.

On Error GoTo Err_Handler

Dim frm As Access.Form
Dim lst As Access.ListBox
Dim astrObjectName() As String

Dim varRow As Variant
Dim strValue As String
Dim I As Integer

IsSelectedInListbox = False

If IsNull(pValue) Then Exit Function

astrObjectName = Split(pListboxRef, "!")
I = LBound(astrObjectName)
Set frm = Forms(astrObjectName(I))
I = I + 1
While I < UBound(astrObjectName)
Set frm = frm(astrObjectName(I)).Form
I = I + 1
Wend
Set lst = frm(astrObjectName(I))

strValue = CStr(pValue)

For Each varRow In lst.ItemsSelected
If lst.ItemData(varRow) = strValue Then
IsSelectedInListbox = True
Exit For
End If
Next varRow

Exit_Point:
Set lst = Nothing
Set frm = Nothing
Exit Function

Err_Handler:
IsSelectedInListbox = CVErr(Err.Number)
Resume Exit_Point

End Function
'----- end of code -----

I havenb't thoroughly tested it, but it seems to work.

 

[John Spencer]

You could probably use the SQL statement you have created; however, since
you have not posted your VB it is difficult to say.

Did you look at the references I posted? If so, did you have problems
understanding how to adapt one or both approaches to your situation?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[BWD]

You could probably use the SQL statement you have created; however, since
you have not posted your VB it is difficult to say.

Did you look at the references I posted? If so, did you have problems
understanding how to adapt one or both approaches to your situation?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.









- Show quoted text -

John

I was unable to adapt the code that you pointed out to me. The first
reference did not make sense because it looks to be written in VB and
I am not sure how to use this in a query. The second link you
provided I had previously used to build my report data upon. I posted
the VB code for that report below.

Thanks

Private Sub Report_Open(Cancel As Integer)
'This routine looks at all the values selected on the Lease Options
form and creates the "IN" statements
'That will be used in the SQL to generate the report with the multi
selects

Dim SQL As String 'variable used to hold SQL
statement
Dim strAgency2 As String
Dim x As Variant 'a variable to hold the values
selected from the multi list
Dim strdelimiter As String 'delimiter used for working with
text based "'s
Dim lngLen As Long 'variable used to hold length of
values and
Dim strAgency As String 'string to hold the IN clause
for Agency list
Dim strMarket As String 'string to hold the IN clause
for Market list
Dim strCounty As String 'string to hold the IN clause
for County list
Dim strCity As String 'string to hold the IN clause
for City list
Dim dtStartDate As Date 'Variable to hold report
starting date parameter
Dim dtEndDate As Date 'Variable to hold report ending
date parameter

On Error GoTo myerrhandler

strdelimiter = "'"

'check if the start date is blank and include all records
If Forms!frmSOFLeases.Start_date = "" Then
dtStartDate = #1/1/1900#
Else
dtStartDate = Forms!frmSOFLeases.Start_date
End If

'check if the end date is blank and include all records
If Forms!frmSOFLeases.End_date = "" Then
dtEndDate = #1/1/3000#
Else
dtEndDate = Forms!frmSOFLeases.End_date
End If

'check the values of each of the list boxes and ensure that at least 1
' value is selected from each of the 4 list boxes
If Forms!frmSOFLeases.lstbx_Agency.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the Agency list", vbOKOnly,
"Select a value!"
DoCmd.CancelEvent
Exit Sub
ElseIf Forms!frmSOFLeases.lstbox_Market.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the Market list", vbOKOnly,
"Select a value!"
ElseIf Forms!frmSOFLeases.lstbox_County.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the County list", vbOKOnly,
"Select a value!"
ElseIf Forms!frmSOFLeases.lstbox_City.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the City list", vbOKOnly, "Select
a value!"
'Else: DoCmd.OpenReport strReportName, acViewPreview
End If


'Find all values selected in the Agency list box and create the IN
statement
With Forms!frmSOFLeases.lstbx_Agency 'Look at the list
box with the agency values
For Each x In .ItemsSelected 'create routine to
loop through the selected values
If Not IsNull(x) Then 'check for nulls
strAgency = strAgency & strdelimiter & .ItemData(x) &
strdelimiter & "," 'Set strAgency equal to all values selected
End If
Next
End With

'Trim the strAgency variable and add the "IN clause used in the query
If Len(strAgency) > 0 Then
lngLen = Len(strAgency) - 1 'Trim the last
comma off of strAgency
strAgency = "[Agency] IN (" & Left(strAgency, lngLen) & ")"
'strAgency now contains the IN statement needed
End If

x = "" 'reset "X" equal
to nothing

'Find all values selected in the Market list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_Market
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strMarket = strMarket & strdelimiter & .ItemData(x) &
strdelimiter & "," 'store values in strMarket
End If
Next
End With

If Len(strMarket) > 0 Then
lngLen = Len(strMarket) - 1 'Trim the last comma from
strMarket
strMarket = "[Market] IN (" & Left(strMarket, lngLen) & ")"
'strMarket now contains the IN statement needed
End If

x = "" 'reset "X" equal
to nothing

' Find all values selected in the County list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_County
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strCounty = strCounty & strdelimiter & .ItemData(x) &
strdelimiter & "," 'store values in strCounty
End If
Next
End With

If Len(strCounty) > 0 Then
lngLen = Len(strCounty) - 1 'Trim the last comma
strCounty = "[County] IN (" & Left(strCounty, lngLen) & ")"
'strCounty now contains the IN statement
End If

x = "" 'reset "X" equal
to nothing

' Find all values seleceted in the City list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_City
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strCity = strCity & strdelimiter & .ItemData(x) & strdelimiter
& "," 'store values in strCity
End If
Next
End With

If Len(strCity) > 0 Then
lngLen = Len(strCity) - 1 'Trim the last comma
strCity = "[City] IN (" & Left(strCity, lngLen) & ")" 'strCity now
contains the IN statement
End If

'Generate the SQL statment for the reports record source
SQL = "Select * from [SOF Leases w Commission] Where " & strAgency & "
AND" & strMarket & _
" AND" & strCounty & " AND" & strCity & " AND" & "[Ending Date]
Between " & "#" & dtStartDate & "#" & " and " & "#" & dtEndDate & "#"

DoCmd.RunSQL (SQL)
Me.RecordSource = SQL
DoCmd.Maximize
Exit Sub

myerrhandler:
MsgBox "Error # is : " & Err.Number & Err.Description

End Sub

 

[BWD]

You could probably use the SQL statement you have created; however, since
you have not posted your VB it is difficult to say.

Did you look at the references I posted? If so, did you have problems
understanding how to adapt one or both approaches to your situation?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

- Show quoted text -

John

I was unable to adapt the code that you pointed out to me. The first
reference did not make sense because it looks to be written in VB and
I am not sure how to use this in a query. The second link you
provided I had previously used to build my report data upon. I posted
the VB code for that report below.

Thanks

Private Sub Report_Open(Cancel As Integer)
'This routine looks at all the values selected on the Lease Options
form and creates the "IN" statements
'That will be used in the SQL to generate the report with the multi
selects

Dim SQL As String 'variable used to hold SQL
statement
Dim strAgency2 As String
Dim x As Variant 'a variable to hold the values
selected from the multi list
Dim strdelimiter As String 'delimiter used for working with
text based "'s
Dim lngLen As Long 'variable used to hold length of
values and
Dim strAgency As String 'string to hold the IN clause
for Agency list
Dim strMarket As String 'string to hold the IN clause
for Market list
Dim strCounty As String 'string to hold the IN clause
for County list
Dim strCity As String 'string to hold the IN clause
for City list
Dim dtStartDate As Date 'Variable to hold report
starting date parameter
Dim dtEndDate As Date 'Variable to hold report ending
date parameter

On Error GoTo myerrhandler

strdelimiter = "'"

'check if the start date is blank and include all records
If Forms!frmSOFLeases.Start_date = "" Then
dtStartDate = #1/1/1900#
Else
dtStartDate = Forms!frmSOFLeases.Start_date
End If

'check if the end date is blank and include all records
If Forms!frmSOFLeases.End_date = "" Then
dtEndDate = #1/1/3000#
Else
dtEndDate = Forms!frmSOFLeases.End_date
End If

'check the values of each of the list boxes and ensure that at least 1
' value is selected from each of the 4 list boxes
If Forms!frmSOFLeases.lstbx_Agency.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the Agency list", vbOKOnly,
"Select a value!"
DoCmd.CancelEvent
Exit Sub
ElseIf Forms!frmSOFLeases.lstbox_Market.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the Market list", vbOKOnly,
"Select a value!"
ElseIf Forms!frmSOFLeases.lstbox_County.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the County list", vbOKOnly,
"Select a value!"
ElseIf Forms!frmSOFLeases.lstbox_City.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the City list", vbOKOnly, "Select
a value!"
'Else: DoCmd.OpenReport strReportName, acViewPreview
End If

'Find all values selected in the Agency list box and create the IN
statement
With Forms!frmSOFLeases.lstbx_Agency 'Look at the list
box with the agency values
For Each x In .ItemsSelected 'create routine to
loop through the selected values
If Not IsNull(x) Then 'check for nulls
strAgency = strAgency & strdelimiter & .ItemData(x) &
strdelimiter & "," 'Set strAgency equal to all values selected
End If
Next
End With

'Trim the strAgency variable and add the "IN clause used in the query
If Len(strAgency) > 0 Then
lngLen = Len(strAgency) - 1 'Trim the last
comma off of strAgency
strAgency = "[Agency] IN (" & Left(strAgency, lngLen) & ")"
'strAgency now contains the IN statement needed
End If

x = "" 'reset "X" equal
to nothing

'Find all values selected in the Market list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_Market
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strMarket = strMarket & strdelimiter & .ItemData(x) &
strdelimiter & "," 'store values in strMarket
End If
Next
End With

If Len(strMarket) > 0 Then
lngLen = Len(strMarket) - 1 'Trim the last comma from
strMarket
strMarket = "[Market] IN (" & Left(strMarket, lngLen) & ")"
'strMarket now contains the IN statement needed
End If

x = "" 'reset "X" equal
to nothing

' Find all values selected in the County list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_County
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strCounty = strCounty & strdelimiter & .ItemData(x) &
strdelimiter & "," 'store values in strCounty
End If
Next
End With

If Len(strCounty) > 0 Then
lngLen = Len(strCounty) - 1 'Trim the last comma
strCounty = "[County] IN (" & Left(strCounty, lngLen) & ")"
'strCounty now contains the IN statement
End If

x = "" 'reset "X" equal
to nothing

' Find all values seleceted in the City list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_City
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strCity = strCity & strdelimiter & .ItemData(x) & strdelimiter
& "," 'store values in strCity
End If
Next
End With

If Len(strCity) > 0 Then
lngLen = Len(strCity) - 1 'Trim the last comma
strCity = "[City] IN (" & Left(strCity, lngLen) & ")" 'strCity now
contains the IN statement
End If

'Generate the SQL statment for the reports record source
SQL = "Select * from [SOF Leases w Commission] Where " & strAgency & "
AND" & strMarket & _
" AND" & strCounty & " AND" & strCity & " AND" & "[Ending Date]
Between " & "#" & dtStartDate & "#" & " and " & "#" & dtEndDate & "#"

DoCmd.RunSQL (SQL)
Me.RecordSource = SQL
DoCmd.Maximize
Exit Sub

myerrhandler:
MsgBox "Error # is : " & Err.Number & Err.Description

End Sub- Hide quoted text -

- Show quoted text -

I have found the answer!! It involves creating a DAO query that
utilizes the SQL created in VB.
Thanks to everyone for their help. I have posted the answer below.

(Note, the code needs to be modified before publishing becasue it uses
an Append clause to update the data and if the query already exists an
error will occur.)

Private Sub cmd_open_query_Click()

Dim mFilter As String 'variable to hold the filter for the where
clause
Dim i As Variant ' counter
Dim c As Integer
Dim qry As New dao.QueryDef 'query to be created
Dim strSQL As String 'SQL that will be used to run the query


' For each item selected in list box lstClient
With Me.lstbx_Agency
For Each i In .ItemsSelected
mFilter = mFilter & "'" & .ItemData(i) & "', "
Next
End With

' Remove comma and space at end of string
mFilter = Left(mFilter, Len(mFilter) - 2)

' Complete Where clause for SQL string that applies to query
mFilter = "Agency in (" & mFilter & ")"

'create the SQL statement
strSQL = "Select [SOF Leases w Commission].agency " & _
"From [SOF Leases w Commission] " & _
"Where " & mFilter

qry.SQL = strSQL 'set query SQL Statement
qry.Name = "Query1" 'Give query a name
dao.Workspaces(0).Databases(0).QueryDefs.Append qry 'append records
and creates query if it doesnt exist
DoCmd.OpenQuery qry.Name 'open query

End Sub