Solved

Filter report on multi select listbox error

Posted on 2010-11-18
6
387 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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility

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
Comment Utility
Thanks! This worked. May I ask what the code is doing? I understand some of it but not all.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
which part of the codes you don't understand?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:shogun5
Comment Utility
What is the Chr(39) doing and this line:

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

Thanks!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
Thanks!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now