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
shogun5Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.