Query using multi-select list box as criteria

[Rick]

I'm new to Access and this is my first posting. I can't
seem to find the solution in the books I've bought so I've
come here.

I have a form populated with records from a query. The
query uses a 'Search' form for criteria (includes 'From'
date field, 'To' date field, and listbox with names).
When the listbox is multi-select/none, the query finds
that name within the date range. Great.

My problem is how to get the query to use multiple names
from the listbox as criteria. I suspect the solution
requires writing code rather than writing an expression.
In either case, if you have a solution, please be specific
(include sample?) about how to write.

 

[Duane Hookom]

This requires code but I hate writing the same code over and over like the
code that loops through the items selected in a multi-select list box. This
function will accept a list box control as an arguement and return syntax
like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )

 

[Rick Willingham]

Thank you for the specifics. If I understand correctly, I
rename my listbox from List8 to lboNInstallerID, I create
a module that includes the new function "BuildIn". I then
call the function as per your example.

I now have more questions.

#1 My listbox has several
fields; "InstallerID", "InstallerFirstName",
and "InstallerLastName". The listbox is bound to column
1. Does the 'BuildIn' function require that the listbox
have only one field, does it find the value in the bound
column, or does it look for the value of a field that has
a field name derived from the listbox name?

#2 OpenReport and OpenForm have the equivilent of a WHERE
clause, but OpenQuery does not, so I'm still at a loss as
to how to get my existing query to use the values that the
function acquires from the listbox.

#3 I tried the following code (thinking that
the "frmTimeClock" form would open with the records from
the query filtered by the strWhere values).

Private Sub Command62_Click()
Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboNInstallerID)
DoCmd.OpenForm "frmTimeClock", , , strWhere

End Sub

When I click the command62 button, I get a pop-up request
for an InstallerId parameter. When I enter an InstallerID
number in the window and click OK, then the form opens
unfiltered - not exactly the result I was hoping for...

Obviously I'm doing something wrong and would appreciate
help.

-----Original Message-----
This requires code but I hate writing the same code over and over like the
code that loops through the items selected in a multi- select list box. This
function will accept a list box control as an arguement and return syntax
like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData (varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )


--
Duane Hookom
MS Access MVP

I'm new to Access and this is my first posting. I can't
seem to find the solution in the books I've bought so I've
come here.

I have a form populated with records from a query. The
query uses a 'Search' form for criteria (includes 'From'
date field, 'To' date field, and listbox with names).
When the listbox is multi-select/none, the query finds
that name within the date range. Great.

My problem is how to get the query to use multiple names
from the listbox as criteria. I suspect the solution
requires writing code rather than writing an expression.
In either case, if you have a solution, please be specific
(include sample?) about how to write.

.

 

[Duane Hookom]

1. I believe the code will use the bound column unless you specify other
2. I never open queries in a user interface. You could modify the SQL
property of the query using code like:
CurrentDb.QueryDefs("qselYourQuery").SQL = "SELECT...."
3. do you have a field InstallerID in the record source of your form?
To troubleshoot, learn to use MsgBox strWhere or Debug.Print strWhere...

--
Duane Hookom
MS Access MVP

Thank you for the specifics. If I understand correctly, I
rename my listbox from List8 to lboNInstallerID, I create
a module that includes the new function "BuildIn". I then
call the function as per your example.

I now have more questions.

#1 My listbox has several
fields; "InstallerID", "InstallerFirstName",
and "InstallerLastName". The listbox is bound to column
1. Does the 'BuildIn' function require that the listbox
have only one field, does it find the value in the bound
column, or does it look for the value of a field that has
a field name derived from the listbox name?

#2 OpenReport and OpenForm have the equivilent of a WHERE
clause, but OpenQuery does not, so I'm still at a loss as
to how to get my existing query to use the values that the
function acquires from the listbox.

#3 I tried the following code (thinking that
the "frmTimeClock" form would open with the records from
the query filtered by the strWhere values).

Private Sub Command62_Click()
Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboNInstallerID)
DoCmd.OpenForm "frmTimeClock", , , strWhere

End Sub

When I click the command62 button, I get a pop-up request
for an InstallerId parameter. When I enter an InstallerID
number in the window and click OK, then the form opens
unfiltered - not exactly the result I was hoping for...

Obviously I'm doing something wrong and would appreciate
help.

-----Original Message-----
This requires code but I hate writing the same code over and over like the
code that loops through the items selected in a multi- select list box. This
function will accept a list box control as an arguement and return syntax
like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData (varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )


--
Duane Hookom
MS Access MVP

I'm new to Access and this is my first posting. I can't
seem to find the solution in the books I've bought so I've
come here.

I have a form populated with records from a query. The
query uses a 'Search' form for criteria (includes 'From'
date field, 'To' date field, and listbox with names).
When the listbox is multi-select/none, the query finds
that name within the date range. Great.

My problem is how to get the query to use multiple names
from the listbox as criteria. I suspect the solution
requires writing code rather than writing an expression.
In either case, if you have a solution, please be specific
(include sample?) about how to write.

.

 

[Duane Hookom]

There is probably Help somewhere on line regarding DAO object Architecture.

--
Duane Hookom
MS Access MVP

Thank you for your help.

#1. I thought so...
#2. I'm looking up info on QueryDefs in the Access/VB help files, I think

that's the path I need to go down. I have a couple of books with CD's that
I've been using for examples. So far though, I haven't found an example of
this problem. If you know of a book that has a comparable sample that would
help too...

#3. Yes.

Rick...


----- Duane Hookom wrote: -----

1. I believe the code will use the bound column unless you specify other
2. I never open queries in a user interface. You could modify the SQL
property of the query using code like:
CurrentDb.QueryDefs("qselYourQuery").SQL = "SELECT...."
3. do you have a field InstallerID in the record source of your form?
To troubleshoot, learn to use MsgBox strWhere or Debug.Print strWhere...

--
Duane Hookom
MS Access MVP

Thank you for the specifics. If I understand correctly, I
rename my listbox from List8 to lboNInstallerID, I create
a module that includes the new function "BuildIn". I then
call the function as per your example.

I now have more questions.
#1 My listbox has several

fields; "InstallerID", "InstallerFirstName",
and "InstallerLastName". The listbox is bound to column
1. Does the 'BuildIn' function require that the listbox
have only one field, does it find the value in the bound
column, or does it look for the value of a field that has
a field name derived from the listbox name?

#2 OpenReport and OpenForm have the equivilent of a WHERE

clause, but OpenQuery does not, so I'm still at a loss as
to how to get my existing query to use the values that the
function acquires from the listbox.

#3 I tried the following code (thinking that

the "frmTimeClock" form would open with the records from
the query filtered by the strWhere values).

Private Sub Command62_Click()

Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboNInstallerID)
DoCmd.OpenForm "frmTimeClock", , , strWhere

End Sub
When I click the command62 button, I get a pop-up request

for an InstallerId parameter. When I enter an InstallerID
number in the window and click OK, then the form opens
unfiltered - not exactly the result I was hoping for...

Obviously I'm doing something wrong and would appreciate help.
-----Original Message-----
This requires code but I hate writing the same code over and over like the
code that loops through the items selected in a multi- select list box. This
function will accept a list box control as an arguement and return syntax
like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.
A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere
Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select
If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData (varItem) &>>strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn
End Function
You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like
Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:
strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )
--
Duane Hookom
MS Access MVP
I'm new to Access and this is my first posting. I can't
seem to find the solution in the books I've bought so I've
come here.
I have a form populated with records from a query. The
query uses a 'Search' form for criteria (includes 'From'
date field, 'To' date field, and listbox with names).
When the listbox is multi-select/none, the query finds
that name within the date range. Great.
My problem is how to get the query to use multiple names
from the listbox as criteria. I suspect the solution
requires writing code rather than writing an expression.
In either case, if you have a solution, please be specific
(include sample?) about how to write.
.

 

[Duane Hookom]

Glad to hear you got this working. A couple suggestions from years of
experience and doing things the wrong way:
1) when giving credit to another programmer, spell their last name correctly
2) take the time to give your controls descriptive names.
Text2, Command62, List39, ... do yourself a favor...
Find a great naming convention and follow it religiously.
Check Tony's site http://www.granite.ab.ca/access/tablefieldnaming.htm
3) I find it easier to maintain the error handling code by using
Select Case Err
Case 3265

Case 5

Case Else
End Select
I like your use of comments. I sometimes start with comments on what I need
to do in the code and then add the code.

--
Duane Hookom
MS Access MVP

Hi Duane!

I wanted to thank you again for providing you BuildIn function. It has

taken me until today to figure out how to use it.

My DB still needs work, but I was obsessed with making this filtering

issue work before I moved on. The code that uses the BuildIn function is
the first working code I have written from scratch. I had to modify the
listbox's RowSource in order for another borrowed function (LFSort) to work.
Now, everything's working the way I want.

If you don't mind taking a look at the code, I would appreciate any

suggestions or corrections you might think are needed. I have added alot of
comments to the code - please review them and correct me if the
comment/explanation in incorrect.

Private Sub Command62_Click()
'This is an attempt to use a multi-select listbox and
'modify my query by using Duane Hookum's BuildIn function
'This starts error trapping
On Error GoTo Err_Command62_Click
'These are the declarations
Dim strSQL As String
Dim strWhere As String
Dim strIn As String
'This builds the SQL string for use later
strSQL = "SELECT tblTimeClock.TimeClockID, tblTimeClock.TimeClockDate,

tblInstallers.InstallerID, tblInstallers.InstallerFirstName,
tblInstallers.InstallerLastName, tblTimeClock.PunchInTime,
tblTimeClock.PunchOutTime"

strSQL = strSQL & " FROM tblInstallers INNER JOIN tblTimeClock ON tblInstallers.InstallerID=tblTimeClock.InstallerID"
strSQL = strSQL & " WHERE (((tblTimeClock.TimeClockID) > 0) And

((tblTimeClock.TimeClockDate) >= Forms!frmSearchTimeClockRecords!Text0 And
(tblTimeClock.TimeClockDate) <= Forms!frmSearchTimeClockRecords!Text2))"

'This makes the table name into a string
strIn = "tblInstallers"
'This makes the results of the BuildIn function into a string
strWhere = BuildIn(lboNInstallerID)
'This deletes the " AND " from the BuildIn string
strWhere = Right(strWhere, Len(strWhere) - 5)
'This adds the " AND " back into a string with the table name, a "dot", and the BuildIn string
strIn = " AND " & strIn & "." & strWhere
'This adds the combination into the SQL string
strSQL = strSQL & strIn
'This adds an ORDER BY into the SQL string
'It has to be added here in order to be placed after the WHERE clause
strSQL = strSQL & " ORDER BY tblTimeClock.TimeClockDate"
'This opens the Time Clock form
DoCmd.OpenForm "frmTimeClock"
'This sets the RowSource of the listbox in the Time Clock form
Forms!frmTimeClock!List39.RowSource = strSQL

'This will exit the event procedure
Exit_Command62_Click:
Exit Sub

'This takes over if there is an error
Err_Command62_Click:
'If there is no query then skip the delete process line
'and go on to the next line
If Err.Number = 3265 Then
Resume Next
'If there is not a selection made, then a message box
'tells the user to make a selection.
ElseIf Err.Number = 5 Then
MsgBox "You MUST select at least one Installer Name."
Resume Exit_Command62_Click
'If there is any other error, put the description of the
'error in a message box.
Else
MsgBox Err.Description
Resume Exit_Command62_Click
End If

End Sub

Thanks again for any comments you might post. And thank you to you and

the rest of the people posting great information on this site.

Rick...

----- Duane Hookom wrote: -----

This requires code but I hate writing the same code over and over like the
code that loops through the items selected in a multi-select list box. This
function will accept a list box control as an arguement and return syntax
like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )


--
Duane Hookom
MS Access MVP

I'm new to Access and this is my first posting. I can't
seem to find the solution in the books I've bought so I've
come here.
query uses a 'Search' form for criteria (includes 'From'
date field, 'To' date field, and listbox with names).
When the listbox is multi-select/none, the query finds
that name within the date range. Great.
from the listbox as criteria. I suspect the solution
requires writing code rather than writing an expression.
In either case, if you have a solution, please be specific
(include sample?) about how to write.