Syntax Error in query expression

[KevLow]

Hi,

I was trying to create a crosstab query programmatically, which the
main purpose is to be able to change the column headings dynamically
depending on user's defined number of months to view data. The code as
follows:

Dim cat As New ADOX.catalog
Dim cmd As New ADODB.Command

cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Procedures("qryPickup_xtab").Command

cmd.CommandText = "PARAMETERS [Forms]![SelectDate]![StartDate]
DateTime, [Forms]![SelectDate]![EndDate] DateTime;TRANSFORM
Sum([qryPickupRate].[Stock_Out]) AS SumOfStock_Out " & _
"SELECT [qryPickupRate].[Co_Name], [qryPickupRate].[BROC_Type] " & _
"FROM qryPickupRate " & _
"WHERE ((([qryPickupRate].[Stock_Date]) Between
[Forms]![SelectDate]![StartDate] And [Forms]![SelectDate]![EndDate])) "
& _
"GROUP BY [qryPickupRate].[Co_Name], [qryPickupRate].[BROC_Type] " & _
"PIVOT Format([Stock_Date], ""mmm yy"") In (" & TotStr & ");"

Set cat.Procedures("qryPickup_xtab").Command = cmd
Set cat = Nothing

Everything runs fine till the last part where I tried to put in a
string named Totstr, which contains the month and year (e.g. Jan 05,Feb
05,Mar 05...)

When debug.print the cmd.commandtext,the last part looks like:
PIVOT Format([Stock_Date], "mmm yy") In (Jan 05,Feb 05,Mar 05)

and this produce an error:
Syntax error(missing operator) in query expression
'Format([Stock_Date], "mmm yy") In (Jan 05,Feb 05,Mar 05)'.

What I don't understand is that if I was to open an existing crosstab
query in design view and put this string Jan 05,Feb 05,Mar 05 into the
column headings field, it would auto format into "Jan 05","Feb 05","Mar
05" and the columns would turn out fine when executed. So, why doesn't
the programmic way of update allow it?

Hope you kind souls can help me out here. Thanks in advance!!

Regards,
Kevin

 

[Guest]

Hi Kevin,

I don't think that VBA corrects entries the way that the query builder does,
but you could probably achieve the same thing by adding leading and trailing
single quotes, and using the replace function to replace all commas in the
expression with a comma surrounded by single quotes. Something like:

"PIVOT Format([Stock_Date], ""mmm yy"") In ('" & Replace(TotStr,",","','") &
"');"

HTH, Ted Allen