CODEWITHSUNDEEP
vbams-access
Mojtaba
Mojtaba

Reputation: 1

Transform Query in Access VBA

I have written a VBA code in Access. But, I received syntax error in TRANSFORM statement. I would be grateful if you could help me.

Sub TransformX1()
    Dim dbs As Database
    Dim strSQL As String
    Set dbs = CurrentDb
    strSQL = "TRANSFORM Sum(BAR1.[TON]) AS SumOfTON" _
             & "SELECT BAR1.[MABD],Sum(BAR1.[TON]) AS [Total Of TON]" _
             & "FROM BAR1" _
             & "WHERE (((BAR1.[MABD]) < 1300) And ((BAR1.[MAGH]) < 1300) And ((BAR1.G) = 1))" _
             & "GROUP BY BAR1.[MABD]" _
             & "PIVOT BAR1.[MAGH]"
    DoCmd.RunSQL strSQL
End Sub

Upvotes: 0

Views: 739

Answers (1)

Sam
Sam

Reputation: 5721

If you examine your strSQL variable just before the RunSQL command, you'll find that you are missing a few spaces. Replace 

"TRANSFORM Sum(BAR1.[TON]) AS SumOfTON" _

with 

"TRANSFORM Sum(BAR1.[TON]) AS SumOfTON " _

and so on.

Upvotes: 1

Related Questions