Multi Select List Box

R

Robert_DubYa

I have a multi select list box that I am trying to use in conjunction with a
query. I have borrowed some code and modified (courtesy of Allen Brown) that
grabs all selected data in the list box (I will paste the code and query
below). I want to pass this data to a query. I am doing so via a module.
My problem is the query (query and module below as well) will run fine if I
select just one item from my listbox, but NO results if I select more than
one item in the list (I know the data is there). I'm not sure where I have
gone wrong, but when I test with a message box all appears fine. I have two
diffrent variables I can pass to the module (I did this for testing) one with
and one without quotes for my "IN" criteria. The query works fine if I use
an "In" statement with the values typed in (not passed from the module) Any
help will be greatly apperciated.


Code from form:

Private Sub Command3_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strDepartmentsNoQuotes As String 'String to use as WhereCondition
Dim strDepartmentsWithQuotes As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strDepartmentsNoQuotes = strDepartmentsNoQuotes & strDelim &
..ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDepartmentsWithQuotes = strDepartmentsWithQuotes & """" &
..Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strDepartmentsNoQuotes) - 1
If lngLen > 0 Then
strDepartmentsNoQuotes = Left$(strDepartmentsNoQuotes, lngLen)
lngLen = Len(strDepartmentsWithQuotes) - 2
If lngLen > 0 Then
strDepartmentsWithQuotes = Left$(strDepartmentsWithQuotes, lngLen)
End If
End If

strDepartments = strDepartmentsNoQuotes
MsgBox strDepartments
DoCmd.OpenQuery "qryStep4-ByDept"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub


MODULE:

Option Compare Database
Public strDepartments As String

Function Departments() As String

Departments = strDepartments
MsgBox Departments

End Function


SQL from Query:

SELECT [tblStep1-HoursQtys].[Employee Name], Trim([tblStep1-HoursQtys]!Dept)
AS Dept, Trim([Business Unit]) AS [Work Cent], [tblStep1-HoursQtys].[Order
Numb], [tblStep1-HoursQtys].[Op Seq], [tblStep1-HoursQtys].[Gl Date],
[tblStep1-HoursQtys].[Shift Code], [tblStep1-HoursQtys].[Qty Completed],
[Hours Worked]/([Total Alot Time]/[Wo Qty]) AS [Standard Completions],
[tblStep1-HoursQtys].[Hours Worked], [Qty Completed]*([Total Alot Time]/[Wo
Qty]) AS [Earned Hours], [Earned Hours]/[Hours Worked] AS Realization
FROM ([tblStep1-HoursQtys] INNER JOIN [tblStep2-WoQty] ON
([tblStep1-HoursQtys].[Order Numb] = [tblStep2-WoQty].[Order Numb]) AND
([tblStep1-HoursQtys].[Order Type] = [tblStep2-WoQty].[Order Type])) INNER
JOIN tblStep3GetOpSeqTime ON ([tblStep1-HoursQtys].[Batch Numb] =
tblStep3GetOpSeqTime.[Batch Numb]) AND ([tblStep1-HoursQtys].[Order Numb] =
tblStep3GetOpSeqTime.[Order Numb]) AND ([tblStep1-HoursQtys].[Order Type] =
tblStep3GetOpSeqTime.[Order Type]) AND ([tblStep1-HoursQtys].[Op Seq] =
tblStep3GetOpSeqTime.[Op Seq])
WHERE (((Trim([tblStep1-HoursQtys]![Dept]))=Departments()));
 
R

Roger Carlson

The code is creating a filter. Seems to me, you want to create a query
instead. On my website (www.rogersaccesslibrary.com), is a small Access
database sample called "CreateQueries2.mdb" which illustrates how to do this
(see Form6). You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=368


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Robert_DubYa said:
I have a multi select list box that I am trying to use in conjunction with
a
query. I have borrowed some code and modified (courtesy of Allen Brown)
that
grabs all selected data in the list box (I will paste the code and query
below). I want to pass this data to a query. I am doing so via a module.
My problem is the query (query and module below as well) will run fine if
I
select just one item from my listbox, but NO results if I select more than
one item in the list (I know the data is there). I'm not sure where I
have
gone wrong, but when I test with a message box all appears fine. I have
two
diffrent variables I can pass to the module (I did this for testing) one
with
and one without quotes for my "IN" criteria. The query works fine if I
use
an "In" statement with the values typed in (not passed from the module)
Any
help will be greatly apperciated.


Code from form:

Private Sub Command3_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strDepartmentsNoQuotes As String 'String to use as
WhereCondition
Dim strDepartmentsWithQuotes As String 'String to use as
WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strDepartmentsNoQuotes = strDepartmentsNoQuotes & strDelim
&
.ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDepartmentsWithQuotes = strDepartmentsWithQuotes & """"
&
.Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strDepartmentsNoQuotes) - 1
If lngLen > 0 Then
strDepartmentsNoQuotes = Left$(strDepartmentsNoQuotes, lngLen)
lngLen = Len(strDepartmentsWithQuotes) - 2
If lngLen > 0 Then
strDepartmentsWithQuotes = Left$(strDepartmentsWithQuotes,
lngLen)
End If
End If

strDepartments = strDepartmentsNoQuotes
MsgBox strDepartments
DoCmd.OpenQuery "qryStep4-ByDept"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub


MODULE:

Option Compare Database
Public strDepartments As String

Function Departments() As String

Departments = strDepartments
MsgBox Departments

End Function


SQL from Query:

SELECT [tblStep1-HoursQtys].[Employee Name],
Trim([tblStep1-HoursQtys]!Dept)
AS Dept, Trim([Business Unit]) AS [Work Cent], [tblStep1-HoursQtys].[Order
Numb], [tblStep1-HoursQtys].[Op Seq], [tblStep1-HoursQtys].[Gl Date],
[tblStep1-HoursQtys].[Shift Code], [tblStep1-HoursQtys].[Qty Completed],
[Hours Worked]/([Total Alot Time]/[Wo Qty]) AS [Standard Completions],
[tblStep1-HoursQtys].[Hours Worked], [Qty Completed]*([Total Alot
Time]/[Wo
Qty]) AS [Earned Hours], [Earned Hours]/[Hours Worked] AS Realization
FROM ([tblStep1-HoursQtys] INNER JOIN [tblStep2-WoQty] ON
([tblStep1-HoursQtys].[Order Numb] = [tblStep2-WoQty].[Order Numb]) AND
([tblStep1-HoursQtys].[Order Type] = [tblStep2-WoQty].[Order Type])) INNER
JOIN tblStep3GetOpSeqTime ON ([tblStep1-HoursQtys].[Batch Numb] =
tblStep3GetOpSeqTime.[Batch Numb]) AND ([tblStep1-HoursQtys].[Order Numb]
=
tblStep3GetOpSeqTime.[Order Numb]) AND ([tblStep1-HoursQtys].[Order Type]
=
tblStep3GetOpSeqTime.[Order Type]) AND ([tblStep1-HoursQtys].[Op Seq] =
tblStep3GetOpSeqTime.[Op Seq])
WHERE (((Trim([tblStep1-HoursQtys]![Dept]))=Departments()));
Click to expand...
 
R

Roger Carlson

Oh, there's also another sample called "MultiSelectListboxes.mdb" which
illustrates it as well:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=315

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Robert_DubYa said:
I have a multi select list box that I am trying to use in conjunction with
a
query. I have borrowed some code and modified (courtesy of Allen Brown)
that
grabs all selected data in the list box (I will paste the code and query
below). I want to pass this data to a query. I am doing so via a module.
My problem is the query (query and module below as well) will run fine if
I
select just one item from my listbox, but NO results if I select more than
one item in the list (I know the data is there). I'm not sure where I
have
gone wrong, but when I test with a message box all appears fine. I have
two
diffrent variables I can pass to the module (I did this for testing) one
with
and one without quotes for my "IN" criteria. The query works fine if I
use
an "In" statement with the values typed in (not passed from the module)
Any
help will be greatly apperciated.


Code from form:

Private Sub Command3_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strDepartmentsNoQuotes As String 'String to use as
WhereCondition
Dim strDepartmentsWithQuotes As String 'String to use as
WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strDepartmentsNoQuotes = strDepartmentsNoQuotes & strDelim
&
.ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDepartmentsWithQuotes = strDepartmentsWithQuotes & """"
&
.Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strDepartmentsNoQuotes) - 1
If lngLen > 0 Then
strDepartmentsNoQuotes = Left$(strDepartmentsNoQuotes, lngLen)
lngLen = Len(strDepartmentsWithQuotes) - 2
If lngLen > 0 Then
strDepartmentsWithQuotes = Left$(strDepartmentsWithQuotes,
lngLen)
End If
End If

strDepartments = strDepartmentsNoQuotes
MsgBox strDepartments
DoCmd.OpenQuery "qryStep4-ByDept"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub


MODULE:

Option Compare Database
Public strDepartments As String

Function Departments() As String

Departments = strDepartments
MsgBox Departments

End Function


SQL from Query:

SELECT [tblStep1-HoursQtys].[Employee Name],
Trim([tblStep1-HoursQtys]!Dept)
AS Dept, Trim([Business Unit]) AS [Work Cent], [tblStep1-HoursQtys].[Order
Numb], [tblStep1-HoursQtys].[Op Seq], [tblStep1-HoursQtys].[Gl Date],
[tblStep1-HoursQtys].[Shift Code], [tblStep1-HoursQtys].[Qty Completed],
[Hours Worked]/([Total Alot Time]/[Wo Qty]) AS [Standard Completions],
[tblStep1-HoursQtys].[Hours Worked], [Qty Completed]*([Total Alot
Time]/[Wo
Qty]) AS [Earned Hours], [Earned Hours]/[Hours Worked] AS Realization
FROM ([tblStep1-HoursQtys] INNER JOIN [tblStep2-WoQty] ON
([tblStep1-HoursQtys].[Order Numb] = [tblStep2-WoQty].[Order Numb]) AND
([tblStep1-HoursQtys].[Order Type] = [tblStep2-WoQty].[Order Type])) INNER
JOIN tblStep3GetOpSeqTime ON ([tblStep1-HoursQtys].[Batch Numb] =
tblStep3GetOpSeqTime.[Batch Numb]) AND ([tblStep1-HoursQtys].[Order Numb]
=
tblStep3GetOpSeqTime.[Order Numb]) AND ([tblStep1-HoursQtys].[Order Type]
=
tblStep3GetOpSeqTime.[Order Type]) AND ([tblStep1-HoursQtys].[Op Seq] =
tblStep3GetOpSeqTime.[Op Seq])
WHERE (((Trim([tblStep1-HoursQtys]![Dept]))=Departments()));
Click to expand...
 
R

Robert_DubYa

Roger, thanks for both replies. I think I understand your logic. Your code
is basically the same. Why can I not pass a string into a module and use the
module as criteria instead of deleting and recreating a query? I am asking
as a learning exercise not at all critical (some people can get touchy when
they give you a good answer and you question them on it).

Thanks for help!
Robert

Roger Carlson said:
Oh, there's also another sample called "MultiSelectListboxes.mdb" which
illustrates it as well:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=315

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Robert_DubYa said:
I have a multi select list box that I am trying to use in conjunction with
a
query. I have borrowed some code and modified (courtesy of Allen Brown)
that
grabs all selected data in the list box (I will paste the code and query
below). I want to pass this data to a query. I am doing so via a module.
My problem is the query (query and module below as well) will run fine if
I
select just one item from my listbox, but NO results if I select more than
one item in the list (I know the data is there). I'm not sure where I
have
gone wrong, but when I test with a message box all appears fine. I have
two
diffrent variables I can pass to the module (I did this for testing) one
with
and one without quotes for my "IN" criteria. The query works fine if I
use
an "In" statement with the values typed in (not passed from the module)
Any
help will be greatly apperciated.


Code from form:

Private Sub Command3_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strDepartmentsNoQuotes As String 'String to use as
WhereCondition
Dim strDepartmentsWithQuotes As String 'String to use as
WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strDepartmentsNoQuotes = strDepartmentsNoQuotes & strDelim
&
.ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDepartmentsWithQuotes = strDepartmentsWithQuotes & """"
&
.Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strDepartmentsNoQuotes) - 1
If lngLen > 0 Then
strDepartmentsNoQuotes = Left$(strDepartmentsNoQuotes, lngLen)
lngLen = Len(strDepartmentsWithQuotes) - 2
If lngLen > 0 Then
strDepartmentsWithQuotes = Left$(strDepartmentsWithQuotes,
lngLen)
End If
End If

strDepartments = strDepartmentsNoQuotes
MsgBox strDepartments
DoCmd.OpenQuery "qryStep4-ByDept"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub


MODULE:

Option Compare Database
Public strDepartments As String

Function Departments() As String

Departments = strDepartments
MsgBox Departments

End Function


SQL from Query:

SELECT [tblStep1-HoursQtys].[Employee Name],
Trim([tblStep1-HoursQtys]!Dept)
AS Dept, Trim([Business Unit]) AS [Work Cent], [tblStep1-HoursQtys].[Order
Numb], [tblStep1-HoursQtys].[Op Seq], [tblStep1-HoursQtys].[Gl Date],
[tblStep1-HoursQtys].[Shift Code], [tblStep1-HoursQtys].[Qty Completed],
[Hours Worked]/([Total Alot Time]/[Wo Qty]) AS [Standard Completions],
[tblStep1-HoursQtys].[Hours Worked], [Qty Completed]*([Total Alot
Time]/[Wo
Qty]) AS [Earned Hours], [Earned Hours]/[Hours Worked] AS Realization
FROM ([tblStep1-HoursQtys] INNER JOIN [tblStep2-WoQty] ON
([tblStep1-HoursQtys].[Order Numb] = [tblStep2-WoQty].[Order Numb]) AND
([tblStep1-HoursQtys].[Order Type] = [tblStep2-WoQty].[Order Type])) INNER
JOIN tblStep3GetOpSeqTime ON ([tblStep1-HoursQtys].[Batch Numb] =
tblStep3GetOpSeqTime.[Batch Numb]) AND ([tblStep1-HoursQtys].[Order Numb]
=
tblStep3GetOpSeqTime.[Order Numb]) AND ([tblStep1-HoursQtys].[Order Type]
=
tblStep3GetOpSeqTime.[Order Type]) AND ([tblStep1-HoursQtys].[Op Seq] =
tblStep3GetOpSeqTime.[Op Seq])
WHERE (((Trim([tblStep1-HoursQtys]![Dept]))=Departments()));
Click to expand...
Click to expand...
 
R

Roger Carlson

No particular reason, I guess. Like most people, when I find a solution
that works, I stick with it.

There's no reason that I can see that you can't do as you say. However,
that doesn't seem to be what you are doing in your original post.

You would need a Function (say ReturnList()) whose argument would be the
name of a multi-select listbox control. The function would parse the values
in much the same way Allen's code does, and return a comma delimited string.
Lastly, you would put the function in the query, feeding the control name.
Something like this:

SELECT * FROM MyTable WHERE MyField IN(ReturnList(forms!myform!mycontrol))

As I say, this will *probably* work, although I've not tested it. One
problem is that the form must be open and values selected for the query to
work. Also, the function will run for each row in the query. Probably not
a big issue, but creating and saving the query with actual values will only
execute your parsing code once.

Hope this helps some.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Robert_DubYa said:
Roger, thanks for both replies. I think I understand your logic. Your
code
is basically the same. Why can I not pass a string into a module and use
the
module as criteria instead of deleting and recreating a query? I am
asking
as a learning exercise not at all critical (some people can get touchy
when
they give you a good answer and you question them on it).

Thanks for help!
Robert

Roger Carlson said:
Oh, there's also another sample called "MultiSelectListboxes.mdb" which
illustrates it as well:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=315

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Robert_DubYa said:
I have a multi select list box that I am trying to use in conjunction
with
a
query. I have borrowed some code and modified (courtesy of Allen
Brown)
that
grabs all selected data in the list box (I will paste the code and
query
below). I want to pass this data to a query. I am doing so via a
module.
My problem is the query (query and module below as well) will run fine
if
I
select just one item from my listbox, but NO results if I select more
than
one item in the list (I know the data is there). I'm not sure where I
have
gone wrong, but when I test with a message box all appears fine. I
have
two
diffrent variables I can pass to the module (I did this for testing)
one
with
and one without quotes for my "IN" criteria. The query works fine if I
use
an "In" statement with the values typed in (not passed from the module)
Any
help will be greatly apperciated.


Code from form:

Private Sub Command3_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strDepartmentsNoQuotes As String 'String to use as
WhereCondition
Dim strDepartmentsWithQuotes As String 'String to use as
WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strDepartmentsNoQuotes = strDepartmentsNoQuotes &
strDelim
&
.ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDepartmentsWithQuotes = strDepartmentsWithQuotes &
""""
&
.Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strDepartmentsNoQuotes) - 1
If lngLen > 0 Then
strDepartmentsNoQuotes = Left$(strDepartmentsNoQuotes, lngLen)
lngLen = Len(strDepartmentsWithQuotes) - 2
If lngLen > 0 Then
strDepartmentsWithQuotes = Left$(strDepartmentsWithQuotes,
lngLen)
End If
End If

strDepartments = strDepartmentsNoQuotes
MsgBox strDepartments
DoCmd.OpenQuery "qryStep4-ByDept"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub


MODULE:

Option Compare Database
Public strDepartments As String

Function Departments() As String

Departments = strDepartments
MsgBox Departments

End Function


SQL from Query:

SELECT [tblStep1-HoursQtys].[Employee Name],
Trim([tblStep1-HoursQtys]!Dept)
AS Dept, Trim([Business Unit]) AS [Work Cent],
[tblStep1-HoursQtys].[Order
Numb], [tblStep1-HoursQtys].[Op Seq], [tblStep1-HoursQtys].[Gl Date],
[tblStep1-HoursQtys].[Shift Code], [tblStep1-HoursQtys].[Qty
Completed],
[Hours Worked]/([Total Alot Time]/[Wo Qty]) AS [Standard Completions],
[tblStep1-HoursQtys].[Hours Worked], [Qty Completed]*([Total Alot
Time]/[Wo
Qty]) AS [Earned Hours], [Earned Hours]/[Hours Worked] AS Realization
FROM ([tblStep1-HoursQtys] INNER JOIN [tblStep2-WoQty] ON
([tblStep1-HoursQtys].[Order Numb] = [tblStep2-WoQty].[Order Numb]) AND
([tblStep1-HoursQtys].[Order Type] = [tblStep2-WoQty].[Order Type]))
INNER
JOIN tblStep3GetOpSeqTime ON ([tblStep1-HoursQtys].[Batch Numb] =
tblStep3GetOpSeqTime.[Batch Numb]) AND ([tblStep1-HoursQtys].[Order
Numb]
=
tblStep3GetOpSeqTime.[Order Numb]) AND ([tblStep1-HoursQtys].[Order
Type]
=
tblStep3GetOpSeqTime.[Order Type]) AND ([tblStep1-HoursQtys].[Op Seq] =
tblStep3GetOpSeqTime.[Op Seq])
WHERE (((Trim([tblStep1-HoursQtys]![Dept]))=Departments()));
Click to expand...
Click to expand...
Click to expand...
 
R

Robert_DubYa

Thanks again Roger. I ended up using your solution. One thing I found
though, it was easier to create a new query off the first query to select my
data from (I had 3 diffrent tables and many rows). This kept my sql simple.
Much gratitude goes out to you and those like you who save the people like me
much frustration!

Roger Carlson said:
Oh, there's also another sample called "MultiSelectListboxes.mdb" which
illustrates it as well:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=315

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Robert_DubYa said:
I have a multi select list box that I am trying to use in conjunction with
a
query. I have borrowed some code and modified (courtesy of Allen Brown)
that
grabs all selected data in the list box (I will paste the code and query
below). I want to pass this data to a query. I am doing so via a module.
My problem is the query (query and module below as well) will run fine if
I
select just one item from my listbox, but NO results if I select more than
one item in the list (I know the data is there). I'm not sure where I
have
gone wrong, but when I test with a message box all appears fine. I have
two
diffrent variables I can pass to the module (I did this for testing) one
with
and one without quotes for my "IN" criteria. The query works fine if I
use
an "In" statement with the values typed in (not passed from the module)
Any
help will be greatly apperciated.


Code from form:

Private Sub Command3_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list
box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strDepartmentsNoQuotes As String 'String to use as
WhereCondition
Dim strDepartmentsWithQuotes As String 'String to use as
WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.

'Loop through the ItemsSelected in the list box.
With Me.lstDept
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strDepartmentsNoQuotes = strDepartmentsNoQuotes & strDelim
&
.ItemData(varItem) & strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDepartmentsWithQuotes = strDepartmentsWithQuotes & """"
&
.Column(0, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strDepartmentsNoQuotes) - 1
If lngLen > 0 Then
strDepartmentsNoQuotes = Left$(strDepartmentsNoQuotes, lngLen)
lngLen = Len(strDepartmentsWithQuotes) - 2
If lngLen > 0 Then
strDepartmentsWithQuotes = Left$(strDepartmentsWithQuotes,
lngLen)
End If
End If

strDepartments = strDepartmentsNoQuotes
MsgBox strDepartments
DoCmd.OpenQuery "qryStep4-ByDept"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub


MODULE:

Option Compare Database
Public strDepartments As String

Function Departments() As String

Departments = strDepartments
MsgBox Departments

End Function


SQL from Query:

SELECT [tblStep1-HoursQtys].[Employee Name],
Trim([tblStep1-HoursQtys]!Dept)
AS Dept, Trim([Business Unit]) AS [Work Cent], [tblStep1-HoursQtys].[Order
Numb], [tblStep1-HoursQtys].[Op Seq], [tblStep1-HoursQtys].[Gl Date],
[tblStep1-HoursQtys].[Shift Code], [tblStep1-HoursQtys].[Qty Completed],
[Hours Worked]/([Total Alot Time]/[Wo Qty]) AS [Standard Completions],
[tblStep1-HoursQtys].[Hours Worked], [Qty Completed]*([Total Alot
Time]/[Wo
Qty]) AS [Earned Hours], [Earned Hours]/[Hours Worked] AS Realization
FROM ([tblStep1-HoursQtys] INNER JOIN [tblStep2-WoQty] ON
([tblStep1-HoursQtys].[Order Numb] = [tblStep2-WoQty].[Order Numb]) AND
([tblStep1-HoursQtys].[Order Type] = [tblStep2-WoQty].[Order Type])) INNER
JOIN tblStep3GetOpSeqTime ON ([tblStep1-HoursQtys].[Batch Numb] =
tblStep3GetOpSeqTime.[Batch Numb]) AND ([tblStep1-HoursQtys].[Order Numb]
=
tblStep3GetOpSeqTime.[Order Numb]) AND ([tblStep1-HoursQtys].[Order Type]
=
tblStep3GetOpSeqTime.[Order Type]) AND ([tblStep1-HoursQtys].[Op Seq] =
tblStep3GetOpSeqTime.[Op Seq])
WHERE (((Trim([tblStep1-HoursQtys]![Dept]))=Departments()));
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

ADP list box filter 3
TRYING Allen B'S multi-select list box 2
2 Multiple List Boxes, Now getting Error 3075 5
RTF or XL report filtered (Allen Browne) 4
Crosstab Query Building Report 2
Create Report On the Fly 1
Two multi-select list boxes 3
Two Multi-Select List Boxes as Filters 11

Top