K
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
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