By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,510 Members | 1,810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,510 IT Pros & Developers. It's quick & easy.

Syntax Error when trying to modify crosstab query

P: n/a
Hi,

Hope some kind soul can help me out here..

I'm trying to programmatically modify the column headings of a crosstab
query such that it can be dynamic based on user specified period (Month
Year to Month Year)

So far i have tried to use the following code:
//
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 & ");"

Debug.Print cmd.CommandText

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

Everthing runs fine till the last part where the
"PIVOT Format([Stock_Date], ""mmm yy"") In (" & TotStr & ");"

The Totstr is supposed to hold a string e.g. Jan 05,Feb 05,Mar 05...up
till where the user specified the end date.

When executed, I get the Syntax error(missing operator) in query
expression 'Format In (Jan 05,Feb 05,Mar 05)'

However, if I try to set the string to ("Jan 05,Feb 05,Mar 05"). It
will run and modify the query's column headings field but the query
would not run as that's not the correct syntax for month-year. It
probably sees the whole chunk as one string, rather than recognising
the month year value.

I know that the correct syntax should be "Jan 05","Feb 05","Mar 05" but
it also worked when I typed Jan 05,Feb 05,Mar 05 directly into the
column headings of the corsstab query. the query is able to recognise
and convert the text to "Jan 05","Feb 05","Mar 05". Therefore, I
thought using progam to update would get the same result but apparently
not.

Pls help advise on how I can resolve or work around this problem.
Thanks in advance.

Kevin

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Anyone please?

KevLow wrote:
Hi,

Hope some kind soul can help me out here..

I'm trying to programmatically modify the column headings of a crosstab query such that it can be dynamic based on user specified period (Month Year to Month Year)

So far i have tried to use the following code:
//
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 & ");"

Debug.Print cmd.CommandText

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

Everthing runs fine till the last part where the
"PIVOT Format([Stock_Date], ""mmm yy"") In (" & TotStr & ");"

The Totstr is supposed to hold a string e.g. Jan 05,Feb 05,Mar 05...up till where the user specified the end date.

When executed, I get the Syntax error(missing operator) in query
expression 'Format In (Jan 05,Feb 05,Mar 05)'

However, if I try to set the string to ("Jan 05,Feb 05,Mar 05"). It
will run and modify the query's column headings field but the query
would not run as that's not the correct syntax for month-year. It
probably sees the whole chunk as one string, rather than recognising
the month year value.

I know that the correct syntax should be "Jan 05","Feb 05","Mar 05" but it also worked when I typed Jan 05,Feb 05,Mar 05 directly into the
column headings of the corsstab query. the query is able to recognise and convert the text to "Jan 05","Feb 05","Mar 05". Therefore, I
thought using progam to update would get the same result but apparently not.

Pls help advise on how I can resolve or work around this problem.
Thanks in advance.

Kevin


Nov 13 '05 #2

P: n/a
Kevin,

I am not familiar with the ADO syntax but I would suggest that you try to modify your
string TotStr to
'Jan 05', 'Feb 05', 'Mar 05' . . . (use single quotes here)

--
Hope this helps
Arno R
"KevLow" <we****@pacific.net.sg> schreef in bericht
news:11********************@g14g2000cwa.googlegrou ps.com...
Anyone please?

KevLow wrote:
Hi,

Hope some kind soul can help me out here..

I'm trying to programmatically modify the column headings of a

crosstab
query such that it can be dynamic based on user specified period

(Month
Year to Month Year)

So far i have tried to use the following code:
//
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 & ");"

Debug.Print cmd.CommandText

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

Everthing runs fine till the last part where the
"PIVOT Format([Stock_Date], ""mmm yy"") In (" & TotStr & ");"

The Totstr is supposed to hold a string e.g. Jan 05,Feb 05,Mar

05...up
till where the user specified the end date.

When executed, I get the Syntax error(missing operator) in query
expression 'Format In (Jan 05,Feb 05,Mar 05)'

However, if I try to set the string to ("Jan 05,Feb 05,Mar 05"). It
will run and modify the query's column headings field but the query
would not run as that's not the correct syntax for month-year. It
probably sees the whole chunk as one string, rather than recognising
the month year value.

I know that the correct syntax should be "Jan 05","Feb 05","Mar 05"

but
it also worked when I typed Jan 05,Feb 05,Mar 05 directly into the
column headings of the corsstab query. the query is able to

recognise
and convert the text to "Jan 05","Feb 05","Mar 05". Therefore, I
thought using progam to update would get the same result but

apparently
not.

Pls help advise on how I can resolve or work around this problem.
Thanks in advance.

Kevin

Nov 13 '05 #3

P: n/a
Hi Arno,

Thanks for the help, it worked. Cheers man! Now, I can move on to
reports...;-)

Arno R wrote:
Kevin,

I am not familiar with the ADO syntax but I would suggest that you try to modify your string TotStr to
'Jan 05', 'Feb 05', 'Mar 05' . . . (use single quotes here)

--
Hope this helps
Arno R
"KevLow" <we****@pacific.net.sg> schreef in bericht
news:11********************@g14g2000cwa.googlegrou ps.com...
Anyone please?

KevLow wrote:
Hi,

Hope some kind soul can help me out here..

I'm trying to programmatically modify the column headings of a

crosstab
query such that it can be dynamic based on user specified period

(Month
Year to Month Year)

So far i have tried to use the following code:
//
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 & ");"

Debug.Print cmd.CommandText

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

Everthing runs fine till the last part where the
"PIVOT Format([Stock_Date], ""mmm yy"") In (" & TotStr & ");"

The Totstr is supposed to hold a string e.g. Jan 05,Feb 05,Mar

05...up
till where the user specified the end date.

When executed, I get the Syntax error(missing operator) in query
expression 'Format In (Jan 05,Feb 05,Mar 05)'

However, if I try to set the string to ("Jan 05,Feb 05,Mar 05").
It will run and modify the query's column headings field but the query would not run as that's not the correct syntax for month-year. It
probably sees the whole chunk as one string, rather than recognising the month year value.

I know that the correct syntax should be "Jan 05","Feb 05","Mar

05" but
it also worked when I typed Jan 05,Feb 05,Mar 05 directly into the
column headings of the corsstab query. the query is able to

recognise
and convert the text to "Jan 05","Feb 05","Mar 05". Therefore, I
thought using progam to update would get the same result but

apparently
not.

Pls help advise on how I can resolve or work around this problem.
Thanks in advance.

Kevin


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.