Filter Report using form with list boxes

[Guest]

I am teaching myself VB and it is not going well. I thought I found what I
needed on another web site and I altered the code to reflect my database and
it is not working. I keep getting a data type mismatch.

My form has 5 list boxes. Each of the boxes is based on another table and
most have more than one column. For example the Investigator table has the
primary key and the first and last names. The first column is the bound colum
and the other two are displayed. The table that the report is nuilt on is
mainly llokup fields to these tables and thus they are numeric fields.

The code I am using is as follows:
For each varItem in Me.lastName.Itemselected
strName = strName & ",'" & MelastName.ItemData(varItem) & "'"
Next varItem
If Len(strName) = 0 Then
strName = "Like '*'"
Else
strName = Right(strName, Len(strName) -1)
End If


I am using Access 2002

 

[Guest]

It's not entirely clear what your code's intent is. Are you trying to build
a WHERE clause from multiple selections in this list box? Or do you just
want to display the records in the report that satisfy all of the selections
in the five list boxes? If it's the latter, then it might be easier to just
pass the value of the bound columns to the report's WHERE clause when the
report is opened. For example (watch out for word wrap):

DoCmd.OpenReport "rptSales", acViewPreview, , _
"(ID = " & Me!lstEmployee.Column(0) & ") AND (State = '" & _
Me!lstState.Column(0) & "')"

.. . . where rptSales is the name of the report, lstEmployeeis the list box
displaying the employees' names with the first column (0) as the column bound
to the ID (numerical) field, and lstState is the name of the list box
displaying the list of state abbreviations with the first column (0) as the
column bound to the State (text) field. ID is the primary key for one of the
tables that the report is bound to, and State is the primary key for the
table that the lstState list box is bound to.

If it's the former -- or something else entirely -- then please explain
and/or show more code as to how strName and the other list box choices are
going to be passed to the report in the filter.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

I will try this, however I am concerned that it is vastly differnt from what
I was using I assumed I had made a small error and not the whole design of
the code was wrong.

In the example I was using as a guide for my code there was a criteria built
for each list box which was suppose to pull the value(s) and/or return all if
there was no selection. After that being done there was a where clause to
combine all the criteria and apply them to the report.

What I thought may have been the problem was that the example I was using as
a guide was using strings because their list boxes were text but my list
boxes are technically numeric because they are look up fields I thought
mayvbe I would need to adjust my code in someway that I was unaware of.

Amy