Multi Select Simple Returning No Results

[jsimard]

I have set up a query with a parameter asking the user to choose a city they want to see the data for. I created a form with a list box displaying all the possible choices. When I run the report it works fine displaying the city they have chosen. I would like the user to be able to choose more than one city and display the data for each in the report. When I change the property setting for multi select to simple or extended my report does not display any results. Any suggestions???

[boblarson]

You can't use a multi-select list box as criteria on the query. You would need to do it in code as you have to iterate through the listbox to get the selected items.

A good way to do this is to remove the criteria from the query and then use the Where Clause of the DoCmd.OpenReport code to send the values. Something like this:

Dim strWhere As String
Dim varItem As Variant
 
If Me.ListBoxNameHere.ItemsSelected.Count > 0 Then
   For Each varItem In Me.ListBoxNameHere.SelectedItems
        strWhere = strWhere & Chr(34) & Me.ListBoxNameHere.ItemData(varItem) & Chr(34) & ","
   Next
   strWhere = Left(strWhere, Len(strWhere)-1)
   strWhere = "[CityFieldNameHere] In(" & strWhere & ")"
 
DoCmd.OpenReport "ReportNameHere", acViewPreview, , strWhere

[jsimard]

I'm sorry, I am not very familiar with using code in Access. Am I supposed to put this code in the "on click" event procedure for the command button that will open the report? I did this and I get a run-time error '3075': Syntax error in string in query expression '([City]In("thecityname))'.

[boblarson]

I'm sorry, I am not very familiar with using code in Access. Am I supposed to put this code in the "on click" event procedure for the command button that will open the report? I did this and I get a run-time error '3075': Syntax error in string in query expression '([City]In("thecityname))'.

Yes, it would go in the click event that opens the report. Please post the rest of the code so we can troubleshoot.

[jsimard]

Yes, it would go in the click event that opens the report. Please post the rest of the code so we can troubleshoot.

Private Sub cmdPreviewReport_Click()
Dim strWhere As String
Dim varItem As Variant
If Me.City.ItemsSelected.Count > 0 Then
For Each varItem In Me.City.ItemsSelected
strWhere = strWhere & Chr(34) & Me.City.ItemData(varItem) & Chr(34)
Next
strWhere = Left(strWhere, Len(strWhere) - 1)
strWhere = "[City] In(" & strWhere & ")"
DoCmd.OpenReport "DER Next Visit", acViewPreview, , strWhere
End If
End Sub

[boblarson]

Okay, I see the problem. Sorry about that - I left something off:
EDIT: Well, not really - YOU left something off. I included it in my original code - I just saw above in the original post I made.

Private Sub cmdPreviewReport_Click()
Dim strWhere As String
Dim varItem As Variant
 
If Me.City.ItemsSelected.Count > 0 Then
   For Each varItem In Me.City.ItemsSelected
      strWhere = strWhere & Chr(34) & Me.City.ItemData(varItem) & Chr(34) & ","
   Next
 
strWhere = Left(strWhere, Len(strWhere) - 1)
strWhere = "[City] In(" & strWhere & ")"
 
DoCmd.OpenReport "DER Next Visit", acViewPreview, , strWhere
 
End If
End Sub

[jsimard]

PERFECT. That was my mistake but thank you so much. Works perfectly .

[TinaCa]

Using the code posted previously I doctored it a bit as some options like Listbosname.selectitems wasn't working for me. I am getting the correct information in my strWhere but the report is returning all records instead of just the two selected. Here is the code.

Private Sub cmdViewRpt_Click()
Dim StrWhere As String
Dim varItem As Variant
If Me.lstWorker.ItemsSelected.Count > 0 Then
For Each varItem In Me.lstWorker.ItemsSelected
StrWhere = StrWhere & Me.lstWorker.ItemData(varItem) & " or like "

Next
StrWhere = Left(StrWhere, Len(StrWhere) - 9)
StrWhere = "[crewmemberid] in (StrWhere)"
End If

DoCmd.OpenReport "crewInformation", acViewPreview, StrWhere

Any ideas on why if strWhere = "1 or like 3" that the report shows all (i've also tried "1,3" and "1 or 3") . (It only shows one if I select one item i.e. strwhere = "1")

[boblarson]

TinaCa:

You had a few things out of place.

1. You can't use Or Like and if an item is selected it is a specific item, not a like.
2. We are using the IN operator which uses a COMMA DELIMITED list. So commas we must use.
3. Variables need to be concatenated in to the string.
4. And you were missing a comma in the openReport code.

So here's your code, revised and with some red parts to highlight the above instructions (1 - 3)

Private Sub cmdViewRpt_Click()
    Dim StrWhere As String
    Dim varItem As Variant
 
    If Me.lstWorker.ItemsSelected.Count > 0 Then
 
        For Each varItem In Me.lstWorker.ItemsSelected
            StrWhere = StrWhere & Me.lstWorker.ItemData(varItem) & ", "
        Next
 
        StrWhere = Left(StrWhere, Len(StrWhere) - 2)
        StrWhere = "[crewmemberid] In (" & StrWhere & ")"
    End If
 
    DoCmd.OpenReport "crewInformation", acViewPreview, , StrWhere
 
End Sub

[TinaCa]

Thank You!!! I tried writing the parameter directly into the query to see what work and came up with the "Like" thing. You are awesome.....now i just need to grow back my hair .

[jsimard]

Bob,
I opened my database using Access 2010 and had some minor fixes but when I try opening the report the "chr" is highlighted. Any suggestions:

Private Sub Preview_Report_Click()
Dim strWhere As String
Dim varItem As Variant

If Me.City.ItemsSelected.Count > 0 Then
For Each varItem In Me.City.ItemsSelected
strWhere = strWhere & Chr(34) & Me.City.ItemData(varItem) & Chr(34) & ","
Next

strWhere = Left(strWhere, Len(strWhere) - 1)
strWhere = "[City] In(" & strWhere & ")"

DoCmd.OpenReport "DER - Next Visit Update", acViewPreview, , strWhere

End If
End Sub

[boblarson]

Check the TOOLS > REFERENCES in the VBA window to make sure none are marked as MISSING. If there are, uncheck them and reopen the database. If not, make sure you don't have any tables, fields, queries, forms, reports, macros, modules, or procedures that you have named CHR.