Help with selecting multiple items from a listbox

[Poobear1929]

I am trying to open a report based on a selection from a list box on a form. I don't know if my code is way off or my query is is way off. Here is what I have so far, which is what I got from an old thread from last year regarding this problem.
my form is called ind_fit_test_report
my report is called fitness_individual
and my query is called fitness_individual
The list box is called Date

Private Sub Command9_Click()


Dim Repto As String
Dim frm As Form, ctl As Control
Dim varitem As Variant
Dim str As String

Set frm = Forms!ind_fit_test_report
Repto = "fitness_individual"
str = "[date]"
Set ctl = frm.Date
If ctl.ItemsSelected.Count = 0 Then
MsgBox "No date was selected," & (Chr(13)) & (Chr(13)) & "please select at least one date from list", vbExclamation, "selection Error"
Else
For Each varitem In ctl.ItemsSelected
str = str & ctl.ItemData(varitem) & ","
Next varitem
str = Left$(str, Len(str) - 2)
str = str & ")"
DoCmd.OpenReport Repto, acViewPreview, , str

End If
End Sub

here is my query
SELECT DISTINCT [Final Score].Test_ID, [Final Score].Member_ID, [Final Score].Last_name, [Final Score].First_name, [Final Score].Middle_In, [Final Score].Sex, [Final Score].Age, [Final Score].Date, [Final Score].Ab_circum, [Final Score].Crunchs, [Final Score].Run_Time, [Final Score].Push_ups, [Final Score].Bike_Test, [Final Score].[Total Score], [Final Score].[Fitness Level], Group_lookup.Group
FROM [Final Score], Group_lookup
WHERE ((([Final Score].Member_ID)=[Forms]![Ind_Fit_Test_Report]![Name_combo]) AND ((Group_lookup.Sex)=[FINAL SCORE]![Sex]) AND ((Group_lookup.Age_min)<=[FINAL SCORE]![Age]) AND ((Group_lookup.Age_max)>=[FINAL SCORE]![AGE]));

I am very new to Access, matter of fact this is my first database. So I am really excited to learn.

Poobear1929

 

[PHV]

Try something like this:
Set frm = Forms!ind_fit_test_report
Repto = "fitness_individual"
Set ctl = frm.Date
If ctl.ItemsSelected.Count = 0 Then
MsgBox "No date was selected," & (Chr(13)) & (Chr(13)) & "please select at least one date from list", vbExclamation
Else
str = "[date] IN ("
For Each varitem In ctl.ItemsSelected
str = str & "#" & ctl.ItemData(varitem) & "#,"
Next varitem
str = Left$(str, Len(str) - 1) & ")"
DoCmd.OpenReport Repto, acViewPreview, , str
End If

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Poobear1929]

PH,

Thank you for your help. It runs fine, except when I select two items from the listbox at the same time, only one of the items shows up on the report. Not sure where to go from here. Your help would be wonderful.

Thank you again.

Poobear

 

[Poobear1929]

PH,

Nevermind, I had to adjust my report layout. Silly me. Your code worked like a charm. Thank You so much for your help.

V/R
Poobear