?
Solved

Filter report on multi select listbox error

Posted on 2010-11-18
6
Medium Priority
?
394 Views
Last Modified: 2012-05-10
Experts,

I am filtering a report based on list box selection (see code) But I am getting an error message (see screenshot). I how to I trim off the last 'or' statement so this filter will work?

Thanks!

Private Sub cmdPrint_Click()
Dim var As Variant
Dim sWhere As String

For Each var In Me.lstServiceModel.ItemsSelected
  sWhere = sWhere & "tblStdServiceModel=" & Me.lstServiceModel.Column(0, var) & " OR "
Next

Debug.Print sWhere

DoCmd.OpenReport "rptStudentList", acViewPreview, , sWhere


End Sub

Open in new window

screenshot1.png
0
Comment
Question by:shogun5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 34169918

try this codes

Private Sub cmdPrint_Click()
Dim var As Variant
Dim sWhere As String

For Each var In Me.lstServiceModel.ItemsSelected
  sWhere = sWhere & "," & Chr(39) & Me.lstServiceModel.Column(0, var) & chr(39)  
Next

if len(sWhere)>0 then sWhere=mid(sWhere,2)

DoCmd.OpenReport "rptStudentList", acViewPreview, , "[tblStdServiceModel] In (" & sWhere &")"


End Sub
0
 

Author Closing Comment

by:shogun5
ID: 34169962
Thanks! This worked. May I ask what the code is doing? I understand some of it but not all.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34169985
which part of the codes you don't understand?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shogun5
ID: 34170000
What is the Chr(39) doing and this line:

if len(sWhere)>0 then sWhere=mid(sWhere,2)

Thanks!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170009
chr(39) is single quote,
it wraps the values like this  'Goals Met','Pullout'

if len(sWhere)>0  '<< check if the string variable  is not empty (length > 0)

 mid(sWhere,2)  '<< removes the first comma
0
 

Author Comment

by:shogun5
ID: 34170014
Thanks!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question