Another multi-select list box to pass query criteria

[MikeN]

I have been searching all over for explanations of how to use selected data from a multi-select listbox as criteria in a query but I'm not very well versed in 'more advanced' VBA language. I've tried multiple different explanations that I've found online. I somewhat understand the 'multi-select for query criteria' concept but none of the explanations have worked - most likely because I don't know the language well enough to know what needs to be replaced specifically for my database or if I'm missing necessary fields. I'm using Access 2013.



I created dynamic combo boxes (cmbGroup and cmbInstructor) that filters the values that appear in the listbox -- this works as I wanted -> cmbInstructor updates based on the selection in cmbGroup, and listTripDates updates based on the selection of cmbInstructor. But I want the user to then be able to select one or more values from the listbox that can be used to run a query based on their selections. Attached are a couple images - frm_QueryBuilder is the userinterface; Query1 is the query design that I want made when the user clicks the "Query Belt Survey" button.

Attachment 22796Attachment 22795

[ItsMe]

If you are going to use comboboxes to populate a listbox control, why not use combos to populate a temp table? You can use a subform in datasheet view to display the results and use the results in a query by joining the temp table.

[MikeN]

If you are going to use comboboxes to populate a listbox control, why not use combos to populate a temp table? You can use a subform in datasheet view to display the results and use the results in a query by joining the temp table.

In the end, I am going to have multiple buttons that query different 'survey types' based on the selections. I was just using the one (Belt Survey) to design the code before I move on to the others - which will just be a matter of copy/paste/update links in the code...hopefully.

[pbaldy]

Presuming the goal is a form or report based on the query, you can use this method instead of query criteria:

http://www.baldyweb.com/multiselect.htm

[ItsMe]

EDIT: I'm a slow typist, but I think it is still relevant.

If the options are already in a table and available via a query, then a listbox control will be appropriate. One thing to understand, there is a limit to the number of criteria that can be used. In other words, if a user selects 200 options, you are likely to get an error. If it is a possibility your criteria will exceed (I believe a certain number of characters) then you will want your listbox to append to a temp table.

You will need to iterate your control and evaluate each selection. You can append the selection to a table or you can grab certain information to concatenate a criteria string.

You will need to decide which operators to use. If you mix AND with OR, be sure to get your parenthesis correct. You might want to look into the IN operator, too.
http://www.w3schools.com/sql/sql_in.asp

[MikeN]

Presuming the goal is a form or report based on the query, you can use this method instead of query criteria:

http://www.baldyweb.com/multiselect.htm

I saw that one earlier when I was searching for examples. I failed to mention that the reason I am querying data is so the user can export it in table format statistical analysis, so I would need the results in table form. My thoughts were to create the queries as 'cross-tab' so its easier to work with in excel/statistical programs without much manipulation. Essentially, the only reason I am using the listbox was because I do not want all the data queried for future analysis. Students that belong to many 'groups' and 'instructors' will have access to the data that they collected and entered, but I don't want them to have access to all the data from that specific group/instructor unless they decided to select all the dates that that specific group/instructor combo has collected data for.

[pbaldy]

Then certainly ItsMe's suggestion of joining to a temp table would work. I've added/deleted records from a temp table based on the user selecting/deselecting items in a listbox. Another option would be manipulating the SQL of the query with a DAO QueryDef.

[MikeN]

ItsMe's,
I'm not quite sure how to go about making a temporary table based on user-defined group/instructor/multiple selected dates.


pbaldy,
This is an example of what I was finding earlier with regards to multi-select listbox for query criteria. Do you mean something like this example for manipulating the SQL? If so, what parameters do I need to change to fit my db?

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
If Me!lstRegions.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstRegions.ItemsSelected
strCriteria = strCriteria & "tblData.Region = " & Chr(34) _
& Me!lstRegions.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "tblData.Region Like '*'"
End If
strSQL = "SELECT * FROM tblData " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiSelect"
Set db = Nothing
Set qdf = Nothing
End Sub

[pbaldy]

Yes, that's changing the SQL of the query. You'd need to change all the table, query and field names, listbox name, etc. Rather than open the query at the end you'd do your export.

[MikeN]

pbaldy,
I have made the changes to that example code but now I'm getting Run-time error '3464' Data type mismatch. Debugging highlights DoCmd.OpenQuery "Query1". I left the OpenQuery command so I could see the results immediately. I'll change it to export once I get all the kinks out. Do you have any suggestions for this error? Also, does it look like my changes to the code are correct? The fields for the tables are attached in my first post as an image, if that helps with verification.
Thanks!

The SQL that populates my listbox is:
SELECT tbl_trip.TripID, tbl_trip.TripDate, tbl_trip.Instructor
FROM tbl_Group INNER JOIN tbl_trip ON tbl_Group.SchoolPK = tbl_trip.Affiliation
GROUP BY tbl_trip.TripID, tbl_trip.TripDate, tbl_trip.Instructor
HAVING (((tbl_trip.Instructor)=[Forms]![frm_QueryBuilder]![cmbInstructor]));


Private Sub qryBeltSurvey_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")

If Me!listTripDates.ItemsSelected.Count > 0 Then
For Each varItem In Me!listTripDates.ItemsSelected
strCriteria = strCriteria & "tbl_trip.TripDate = " & Chr(34) _
& Me!listTripDates.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "tbl_Trip.TripDate Like '*'"
End If
strSQL = "SELECT * FROM tbl_trip " & _
"WHERE " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "Query1"
Set db = Nothing
Set qdf = Nothing

End Sub

[pbaldy]

If that's a date/time field, try

strCriteria = strCriteria & "tbl_trip.TripDate = #" & Me!listTripDates.ItemData(varItem) & "# OR "

I personally wouldn't use the Like in the Else, I'd simply drop the WHERE clause completely. You may also find this helpful to debug the SQL:

BaldyWeb-Immediate window