Yes, I misspoke and meant table. Below is the code that I am using to run the query. I have a multi-select listbox that is used to pass criteria into the query (https://www.accessforums.net/access/...ery-56594.html) based on dates selected in the listbox. The query is working as I hoped it would - which will eventually just get exported to Excel. The only reason I am not exporting at this point yet is to be able to see (and adjust) it on-the-fly.
I added the "Delete From" code to try to delete the "old" query results but I get Run-time error '3086' Could not delete from specified tables.
Code:
Docmd.RunSQL "DELETE * FROM BeltQuery"
Code:
Private Sub qryBeltSurvey_Click()
' ------CrossTab Query Layout-------
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim frm As Form, ctl As Control
Set db = CurrentDb()
Set qdf = db.QueryDefs("BeltQuery")
Set frm = Forms!frm_QueryBuilder
Set ctl = frm!listDates
If ctl.ItemsSelected.Count = 0 Then
MsgBox "You must make a selection from the dates list"
Exit Sub
End If
DoCmd.RunSQL "DELETE * FROM BeltQuery" 'delete old records from previous query
For Each varItem In ctl.ItemsSelected
strCriteria = strCriteria & "tbl_trip.TripDate = #" & ctl.ItemData(varItem) & "# OR"
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
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.Notes " & 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 & _
"GROUP BY tbl_trip.TripDate, tbl_trip.Instructor, tbl_trip.SampleMethod, tbl_trip.SampleSite, tbl_BeltSurvey.Observers, tbl_BeltSurvey.TransectNum, tbl_BeltSurvey.Notes " & vbCrLf & _
"PIVOT tbl_beltSurveySpecies.Taxa;"
qdf.SQL = strSQL
DoCmd.OpenQuery "BeltQuery"
Set db = Nothing
Set qdf = Nothing
End Sub