report generated from multi-select list box and date range???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that when it is opened up, it starts out with a dialog box I
made from a form. It has a list box and a date to and date from field.
The list box works ok when it is set to NOT allow multiple selections.
When I set it to allow multiple selections it doesn't return anything on the
report.
Is something going wrong inside my form or report???
Any ideas?
Thanks!
 
A multi-select listbox does not return a value, like a single-select
does. It can only be "read" by looping through the selected items in
code. So, if this is for filtering the report on a field called, say,
Field1, you would construct the Where clause with something like:

Dim vWhere As String
If Forms![The Form name]!List1.ItemsSelected.Count = 0 Then
MsgBox "No items selected."
Exit Sub
End If
For Each i In Forms![The Form name]!List1.ItemsSelected
vWhere = vWhere & ", " & Forms![The Form name]!List1.ItemData(i)
Next
vWhere = Right(vWhere, Len(vWhere) - 2 )
vWhere = "[Field1] In (" & vWhere & ")"

This assumes the listbox name to be List1, and Field1 to be numeric. In
case Field1 is Text, then the For loop should be modified as follows:

For Each i In Forms![The Form name]!List1.ItemsSelected
vWhere = vWhere & ", '" _
& Forms![The Form name]!List1.ItemData(i) & "'"
Next

HTH,
Nikos
 
Hi Nikos,

I am trying to use your code example and my report opens with no data. Can
you please look at my code and tell me what I am doing wrong? My form name
is "PickList" and the list box has several field showing from the record but
the first item is "ItemDescription". Can you also tell me: When I was
debuggin the code I got the error message: missing operator (42', 395'), I
figured out how to show the quotes in the front of the number but - what does
the number mean? Is it a row number?

Dim stDocName As String
Dim vWhere As String
If Forms!PickList!List1.ItemsSelected.Count = 0 Then
MsgBox "No items selected."
Exit Sub
End If
For Each i In Forms!PickList!List1.ItemsSelected
vWhere = vWhere & ",'" & Forms!PickList!List1.ItemData(i) & "'"
Next
vWhere = Right(vWhere, Len(vWhere) - 2)
vWhere = "[ItemDescription] IN ('" & vWhere & ")"

stDocName = "Inventory Pick List"
DoCmd.OpenReport stDocName, acPreview, , vWhere

Thanks in advance.
 
Nikos,
I think it's missing the first item - when I get an error it shows (,4,6 )
(which are the second and third ItemNos that I select) - missing operator in
query expression. Can you see why?

Dim stDocName As String
Dim vWhere As String
If Forms!PickList!List1.ItemsSelected.Count = 0 Then
MsgBox "No items selected."
Exit Sub
End If
For Each i In Forms![PickList]!List1.ItemsSelected
vWhere = vWhere & "," & Forms![PickList]!List1.ItemData(i)
Next
vWhere = Right(vWhere, Len(vWhere) - 2)
vWhere = "[ItemNo] IN (" & vWhere & ")"

DoCmd.OpenReport "Products by Category", acViewPreview, , vWhere
 
Bonnie,

To begin with there is an error in the code, as a result of which the
leading single quote is truncated at the left of vWhere. If you look at
my original code, the separator in the loop was ", '", i.e. there was a
space between the comma and the single quote; in your code, you omitted
that space, so vWhere = Right(vWhere, Len(vWhere) - 2) truncates the
leading comma and the first single quote, instead of the leading comma
and the first space, as it was intended to.

On top of that, I suspect that the listbox maybe returning a numerical
value, when you expect it to return a description; if there is a numeric
PK field in the table where the listbox gets its rowsource from, it is
quite possible that the wizard has included that field and set it as the
bound column, while making it invisible for the user by setting its
column width to 0. If this is the case, chances are the column count
property of the listbox is set to a value equal to number of visible
columns + 1, while some column's width in the Column widths property is
set to 0; this would most likely be the 1st column; its relative
position should be the same as the value in the listbox's Bound Column
property.
Another way to verify is to add a:

Debug.Print Forms!PickList!List1.ItemData(i)

line somewhere in the loop which constructs the vWhere string, and look
at the values returned in the immediate window when you run the code.
If this is indeed the case (numeric value instead of expected text),
then there are two ways you can go:
(1) change the listbox settings to return the description instead, if
you are certain it is not required to return the numeric PK value in
some other part of your project, or
(2) simply make a slight change in your code to pick up the required
column's value in the listbox, by referencing the proper column; so, to
pick up column 2, for instance (the first shown), the code would be:

vWhere = vWhere & ",'" & Forms!PickList!List1.Column(1,i) & "'"

The used index is for the second column is 1, because the column index
is zero based.

HTH,
Nikos
 
Same error as before, you skipped the space in the separator, so you are
truncating off the first (single-digit) value.

HTH,
Nikos
 
I don't know what you mean by that but I finally got it going and it's great.
It will be a very helpful addition to our application.

Thanks Nikos.
 
Back
Top