S
SusanV
I have a bit of code to populate a query which I would then like to export
to excel. I'm stuck on one point though - i get a type mis-match error.
Using debug.print and copying the SQL into a new query the query runs
perfectly - but via VBA I get the error. Can anyone see what is wrong with
this code?
'''''''''''''''''''''''''''''''''
'code start
'''''''''''''''''''''''''''''''''
Dim db As DAO.Database
Dim Rs As Recordset
Dim sql As String
Dim tbl As String
tbl = Me.txttable
sql = "TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat "
_
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl &
".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;"
Debug.Print sql
Set db = CurrentDb()
Set Rs = db.OpenRecordset("TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat "
_
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl &
".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;")
'''''''''''''''''''''''''''''''''
'Code end
'''''''''''''''''''''''''''''''''
to excel. I'm stuck on one point though - i get a type mis-match error.
Using debug.print and copying the SQL into a new query the query runs
perfectly - but via VBA I get the error. Can anyone see what is wrong with
this code?
'''''''''''''''''''''''''''''''''
'code start
'''''''''''''''''''''''''''''''''
Dim db As DAO.Database
Dim Rs As Recordset
Dim sql As String
Dim tbl As String
tbl = Me.txttable
sql = "TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat "
_
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl &
".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;"
Debug.Print sql
Set db = CurrentDb()
Set Rs = db.OpenRecordset("TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat "
_
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl &
".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;")
'''''''''''''''''''''''''''''''''
'Code end
'''''''''''''''''''''''''''''''''