Solved

Filter report on multi select listbox error

Posted on 2010-11-18
6
389 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
  • 3
  • 3
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

831 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