Using List Box to control Records in a Report

[SC in Texas]

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Dale Fye]

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[SC in Texas]

Dale and Dave,
Thanks both of you for replying. You got me off of the mental block I was
stuck on.
SC in Texas

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Dale Fye]

So I don't have to strip off a leading or trailing comma at the conclusion of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Klatuu]

Well, glad you got it working. I don't know that I contributed much. I was
mainly curious about Dale's use of + instead of &. They are different beasts
and I would have use the &, but then Dale is pretty good, so I thought maybe
I could learn a new trick.
(It is not that you can't teach an old dog new tricks. It is that the old
dog doesn't want to be bothered)

So where in Texas? I live in Keller and work in Carrollton.
--
Dave Hargis, Microsoft Access MVP

Dale and Dave,
Thanks both of you for replying. You got me off of the mental block I was
stuck on.
SC in Texas

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[SC in Texas]

So I don't have to strip off a leading or trailing comma at the conclusion of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[SC in Texas]

I work for the City of Lubbock.
Thanks for the help

Well, glad you got it working. I don't know that I contributed much. I was
mainly curious about Dale's use of + instead of &. They are different beasts
and I would have use the &, but then Dale is pretty good, so I thought maybe
I could learn a new trick.
(It is not that you can't teach an old dog new tricks. It is that the old
dog doesn't want to be bothered)

So where in Texas? I live in Keller and work in Carrollton.
--
Dave Hargis, Microsoft Access MVP

Dale and Dave,
Thanks both of you for replying. You got me off of the mental block I was
stuck on.
SC in Texas

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP


:

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Klatuu]

Very clever, had not thought of that. I would go with it being elegant.
As to the casual observer, a line of comment would be in order.

Thanks for the info.
--
Dave Hargis, Microsoft Access MVP

So I don't have to strip off a leading or trailing comma at the conclusion of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[SC in Texas]

Thanks Dale.
I really kind of missed asked the question. I am using the list box to
display the chosen records for the print list. I was trying then to use
Listbox0.rowsource for the where statement on the print command button with
an In(Listbox0.rowsource).
I was forgetting that I needed the "" on the text.
The following is the command button for adding the current record to the
print list.
I then use the stWhere in the Print report command buttons onclick event.

Option Compare Database
Dim stWhere As String
Dim stPrintList As String

Private Sub Command18_Click()
If Me![List16].RowSource = "" Then
stPrintList = Chr$(34) & Me![InvNo] & Chr$(34)
Me![List16].RowSource = Me![InvNo]
Else
stPrintList = stPrintList & "," & Chr$(34) & Me![InvNo] & Chr$(34)
Me![List16].RowSource = Me![List16].RowSource & "," & Me![InvNo]
End If
Me![Text20] = stPrintList
stWhere = "InvNo In (" & stPrintList & ")"
End Sub

The IsSelected field was another option I was looking at but the admin
assistant will only be wanting 1 to 5 invoices per report.
One weakness I have in my form is that I have not given her a method of
removing an invoice from the pick list. Also I am now trying to decide the
best way to export the report to Excel because that is the format that
purchasing was the report sent to them in.

Thanks for all of the help. I check the group often and most of the time
can find a suggestion by reviewing others questions.

SC in Texas

So I don't have to strip off a leading or trailing comma at the conclusion of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Douglas J. Steele]

An argument could be made that using a variant is "more expensive" than
using a string variable, so it's possible that you're not actually saving
anything. The Len function call is a very efficient one: not sure about
Left.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So I don't have to strip off a leading or trailing comma at the conclusion
of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less
understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Out of curiousity, Dale, why are you using + instead of & in this
statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
--
Dave Hargis, Microsoft Access MVP

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call
the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in
parenthesis)
will create the WHERE clause if the function returns some values, but
will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to
include an
IsSelected (Yes/No) field in the table that is the source for your
listbox.
Then, in the click event of the listbox, I update the IsSelected
property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when
items are
clicked in the listbox, but the subsequent query will generally run
quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into
the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[SC in Texas]

Dave,
Now that I have a little more time.
I live out in the country east of Lubbock and work for the City of Lubbock
Street Department as their GIS Coordinator.

Thanks,
Steven Callahan

Well, glad you got it working. I don't know that I contributed much. I was
mainly curious about Dale's use of + instead of &. They are different beasts
and I would have use the &, but then Dale is pretty good, so I thought maybe
I could learn a new trick.
(It is not that you can't teach an old dog new tricks. It is that the old
dog doesn't want to be bothered)

So where in Texas? I live in Keller and work in Carrollton.
--
Dave Hargis, Microsoft Access MVP

Dale and Dave,
Thanks both of you for replying. You got me off of the mental block I was
stuck on.
SC in Texas

Out of curiousity, Dale, why are you using + instead of & in this statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
--
Dave Hargis, Microsoft Access MVP


:

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1, varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in parenthesis)
will create the WHERE clause if the function returns some values, but will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to include an
IsSelected (Yes/No) field in the table that is the source for your listbox.
Then, in the click event of the listbox, I update the IsSelected property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when items are
clicked in the listbox, but the subsequent query will generally run quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Dale Fye]

I guess I could use:

For Each varItem In Me.List0.ItemsSelected
strValue = strValue & "," & Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next
fnMultiListSelections = mid(strValue, 2)

That would avoid the variant, and avoid having to use Len and Left to strip
a trailing comma.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

An argument could be made that using a variant is "more expensive" than
using a string variable, so it's possible that you're not actually saving
anything. The Len function call is a very efficient one: not sure about
Left.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So I don't have to strip off a leading or trailing comma at the conclusion
of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less
understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Out of curiousity, Dale, why are you using + instead of & in this
statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
--
Dave Hargis, Microsoft Access MVP


:

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can call
the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in
parenthesis)
will create the WHERE clause if the function returns some values, but
will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to
include an
IsSelected (Yes/No) field in the table that is the source for your
listbox.
Then, in the click event of the listbox, I update the IsSelected
property of
that record. Then, I can simply write a query that joins the table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when
items are
clicked in the listbox, but the subsequent query will generally run
quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box into
the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Douglas J. Steele]

Yeah, I suppose so.

In the grander scheme of things, I don't thing a difference of a hundredth
of a millisecond is significant enough to get worked up about. <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I guess I could use:

For Each varItem In Me.List0.ItemsSelected
strValue = strValue & "," & Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next
fnMultiListSelections = mid(strValue, 2)

That would avoid the variant, and avoid having to use Len and Left to
strip
a trailing comma.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

An argument could be made that using a variant is "more expensive" than
using a string variable, so it's possible that you're not actually saving
anything. The Len function call is a very efficient one: not sure about
Left.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

So I don't have to strip off a leading or trailing comma at the
conclusion
of
the loop.

Have seen it done with automatically adding a comma after each value is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less
understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Out of curiousity, Dale, why are you using + instead of & in this
statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
--
Dave Hargis, Microsoft Access MVP


:

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the value
in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can
call
the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in
parenthesis)
will create the WHERE clause if the function returns some values,
but
will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to
include an
IsSelected (Yes/No) field in the table that is the source for your
listbox.
Then, in the click event of the listbox, I update the IsSelected
property of
that record. Then, I can simply write a query that joins the table
to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when
items are
clicked in the listbox, but the subsequent query will generally run
quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box
into
the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Dale Fye]

I'm not really worried about miliseconds anyway, I'm more concerned about
readability.

I like to use variants (even with the added "expense") when I build my
criteria strings, so I can do:

strSQL = "SELECT * FROM myTable & ("WHERE " + varCriteria)

Dale

Yeah, I suppose so.

In the grander scheme of things, I don't thing a difference of a hundredth
of a millisecond is significant enough to get worked up about. <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I guess I could use:

For Each varItem In Me.List0.ItemsSelected
strValue = strValue & "," & Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next
fnMultiListSelections = mid(strValue, 2)

That would avoid the variant, and avoid having to use Len and Left to
strip
a trailing comma.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

An argument could be made that using a variant is "more expensive" than
using a string variable, so it's possible that you're not actually
saving
anything. The Len function call is a very efficient one: not sure about
Left.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So I don't have to strip off a leading or trailing comma at the
conclusion
of
the loop.

Have seen it done with automatically adding a comma after each value
is
added, but I didn't like having to clip the leading or trailing comma.
Although I think my code is more elegant, it is probably less
understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Out of curiousity, Dale, why are you using + instead of & in this
statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
--
Dave Hargis, Microsoft Access MVP


:

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the
value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can
call
the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in
parenthesis)
will create the WHERE clause if the function returns some values,
but
will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to
include an
IsSelected (Yes/No) field in the table that is the source for your
listbox.
Then, in the click event of the listbox, I update the IsSelected
property of
that record. Then, I can simply write a query that joins the table
to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when
items are
clicked in the listbox, but the subsequent query will generally run
quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box
into
the where
statement for a report to use?

Thanks in advance,
SC in Texas

 

[Douglas J. Steele]

Presumably you meant

strSQL = "SELECT * FROM myTable & (" WHERE " + varCriteria)

<g>

While I understand what you're saying, I'm not sure I agree with it. Not
everyone who might need to work with the code in your application is
necessarily going to understand the difference between + and &, so in my
opinion you're sacrificing understandability to get readability.

I prefer

strSQL = "SELECT * FROM myTable
If Len(strCriteria) > 0 Then
strSQL = strSQL & " WHERE " & strCriteria
End If

In the end, of course, it's your decision though.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm not really worried about miliseconds anyway, I'm more concerned about
readability.

I like to use variants (even with the added "expense") when I build my
criteria strings, so I can do:

strSQL = "SELECT * FROM myTable & ("WHERE " + varCriteria)

Dale

Yeah, I suppose so.

In the grander scheme of things, I don't thing a difference of a
hundredth of a millisecond is significant enough to get worked up about.
<g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I guess I could use:

For Each varItem In Me.List0.ItemsSelected
strValue = strValue & "," & Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next
fnMultiListSelections = mid(strValue, 2)

That would avoid the variant, and avoid having to use Len and Left to
strip
a trailing comma.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

An argument could be made that using a variant is "more expensive" than
using a string variable, so it's possible that you're not actually
saving
anything. The Len function call is a very efficient one: not sure about
Left.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


So I don't have to strip off a leading or trailing comma at the
conclusion
of
the loop.

Have seen it done with automatically adding a comma after each value
is
added, but I didn't like having to clip the leading or trailing
comma.
Although I think my code is more elegant, it is probably less
understandable
to the casual observer.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Out of curiousity, Dale, why are you using + instead of & in this
statement?
v
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
--
Dave Hargis, Microsoft Access MVP


:

Create a function that looks something like:

Public Function fnMultiListSelections(lst as listbox) as Variant

Dim varItem As Variant
Dim varValue As Variant

varValue = Null
For Each varItem In Me.List0.ItemsSelected
varValue = (varValue + ", ") _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem)
Next

fnMultiListSelections = varValue

End Sub

If the bound column is textual, then you will need to wrap the
value in
quotes, similar to:

varValue = (varValue + ", ") _
& chr$(34) _
& Me.List0.Column(Me.List0.BoundColumn - 1,
varItem) _
& chr$(34)

Then, in a command buttons click event or something event, you can
call
the
function like:

strSQL = "SELECT * FROM yourTable " _
& (" WHERE [SomeField] IN(" _
+ fnMultiListSelections(me.lst_ControlName) _
+ ")" )

The second part of the above line of code (the part wrapped in
parenthesis)
will create the WHERE clause if the function returns some values,
but
will
leave it off if the function returns a NULL (no items selected).

Another way to handle this (I actually prefer it actually) is to
include an
IsSelected (Yes/No) field in the table that is the source for your
listbox.
Then, in the click event of the listbox, I update the IsSelected
property of
that record. Then, I can simply write a query that joins the
table to
another table and add a WHERE clause similar to:

WHERE tbl_ListSource.IsSelected <> 0

This method requires constantly updating the underlying table when
items are
clicked in the listbox, but the subsequent query will generally
run
quicker,
since using the IN( ) clause can be slow.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

How do I get the selected values that are chosen in a list box
into
the where
statement for a report to use?

Thanks in advance,
SC in Texas