Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

Filter report on multi select listbox error

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
shogun5
Asked:
shogun5
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:

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
 
shogun5Author Commented:
Thanks! This worked. May I ask what the code is doing? I understand some of it but not all.
0
 
Rey Obrero (Capricorn1)Commented:
which part of the codes you don't understand?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
shogun5Author Commented:
What is the Chr(39) doing and this line:

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

Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
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
 
shogun5Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now