Clear crosstab query results?

[MikeN]

June7,

This is what I have so far. Hopefully I'm on the right track. The loop is recognizing the listbox selections as TripID and prints the appropriate TripID number. Debugging the initial strWhere prints "TripID", then I trim the "," to get "TripID" (with the words TripID being the actual TripID value). If I choose multiple listbox options then I get "TripID","TripID".



I added the Where to my original strSQL code to try to pass the resulting strWhere results into the SQL. I'm not quite sure if my strWhere is the appropriate format, or if its getting passed in the SQL correctly. I've been getting 'data type mismatch in criteria expression' error on the DoCmd.OpenQuery line when I run it though. I'd be thankful if you have the time to take another look at it and see if I'm headed in the right direction. I re-attached my updated db in case you need to look at anything.

Private Sub qryBeltSurvey_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strWhere As String
    Dim strDescrip As String
    Dim lngLen As Long
    Dim strDelim As String
    Dim strDoc As String
    Dim strSQL As String
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("BeltQuerybyInstructor")
    Set frm = Forms!frm_QueryBuilder
    Set ctl = frm!listDates


    strDelim = """"

'Loop through the ItemsSelected in the list box.
    With ctl
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & "," 
            End If
         Next
    End With
    Debug.Print strWhere   ' --> prints "value",
    
    'trim trailing comma
        strWhere = Left(strWhere, Len(strWhere) - 1)
                Debug.Print strWhere  ' --> prints "value"
        
            strSQL = "TRANSFORM Count(tbl_BeltSurveyData.TotalCount) AS CountOfTotalCount " & vbCrLf & _
                "SELECT tbl_trip.TripDate, tbl_trip.Instructor, tbl_trip.SampleMethod, tbl_trip.SampleSite, tbl_BeltSurvey.Observers, tbl_BeltSurvey.TransectNum, tbl_BeltSurvey.SurveyNotes " & vbCrLf & _
                "FROM tbl_trip INNER JOIN (tbl_BeltSurvey INNER JOIN (tbl_beltSurveySpecies INNER JOIN tbl_BeltSurveyData ON tbl_beltSurveySpecies.BeltSurveySpeciesID = tbl_BeltSurveyData.BeltSurveySpecies) ON tbl_BeltSurvey.BeltSurveyID = tbl_BeltSurveyData.BeltSurveyID) ON tbl_trip.TripID = tbl_BeltSurvey.TripID " & vbCrLf & _
                "WHERE tbl_trip.TripID IN (" & strWhere & ") " & vbCrLf & _
                "GROUP BY tbl_trip.TripID, tbl_trip.TripDate, tbl_trip.Instructor, tbl_trip.SampleMethod, tbl_trip.SampleSite, tbl_trip.TripNotes, tbl_BeltSurvey.Observers, tbl_BeltSurvey.TransectNum, tbl_BeltSurvey.SurveyNotes " & vbCrLf & _
                "PIVOT tbl_beltSurveySpecies.Taxa;"
        
    qdf.SQL = strSQL
    DoCmd.OpenQuery "BeltQuerybyInstructor"
   
End Sub

[June7]

TripID is a number type field, do not want the parameter values enclosed with quote marks. Remove the strDelim concatenation. These delimiters are for text type field parameters.

[MikeN]

TripID is a number type field, do not want the parameter values enclosed with quote marks. Remove the strDelim concatenation. These delimiters are for text type field parameters.

That fixed it! It's working! Thank you!!