I am attempting to build SQL on a form load that will populate a chart on the form. The form is unbound with its sole purpose to hold the chart, no other information is need on the form. I am using the below code to get it together however I keep running into Error 3143 Syntax Error in TRANSFORM Statement.
This is a testing type of form and I just copied the SQL over from a form that I know works without using VBA to build it. The difference being I am wanting to use a multi-select listbox to narrow down the results. The bolded line is where the debugger takes me but that doesn't really help me any.
There is more code under this but it is used for formatting the chart and not relevant to the issue.
Note: The database is being built in Access 2010 desktop version.
This is a testing type of form and I just copied the SQL over from a form that I know works without using VBA to build it. The difference being I am wanting to use a multi-select listbox to narrow down the results. The bolded line is where the debugger takes me but that doesn't really help me any.
There is more code under this but it is used for formatting the chart and not relevant to the issue.
Note: The database is being built in Access 2010 desktop version.
Code:
Private Sub Form_Load()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim chtWeek As Chart
'Set chtWeek = Me.Graph0.Object
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTest")
For Each varItem In Forms!frmChartMenu!lstSuppliers.ItemsSelected
strCriteria = strCriteria & ",'" & Forms!frmChartMenu!lstSuppliers.ItemData(varItem) & "'"
Next varItem
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "PARAMETERS [Forms]![frmChartMenu]![lstSuppliers] Text ( 255 ), [Forms]![frmChartMenu]![txtTargets] Text ( 255 );" & _
"TRANSFORM Count(*) As [Count]" & _
"SELECT Format([FileDate],'Short Date') AS Expr1, [Forms]![frmChartMenu]![txtTargets] AS [DailyGoal] " & _
"FROM tblRawData" & _
"WHERE tblRawData.Supplier IN(" & strCriteria & ")" & _
"GROUP BY (Format([FileDate],'Short Date')), (Int([FileDate])), [Forms]![frmChartMenu]![txtTargets]" & _
"PIVOT qryTest.[Supplier];"
[B]qdf.SQL = strSQL[/B]
DoCmd.OpenQuery "qryTest"
Set db = Nothing
Set qdf = Nothing