473,320 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Syntax Error when trying to modify crosstab query

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
3 3073
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Debbie | last post by:
Hi I have developed an application that access an SQL database and installed it on a different machine The installation is successful but when I try to run the application I get the following...
1
by: Jeff Thur | last post by:
I am getting this error when trying to run a stored procedure: Microsoft.VisualBasic.CompilerServices.LateBinding.LateInd exGet(Object o, Object args, String paramnames) +1361...
1
by: FaYYaZ | last post by:
Hi All I am getting error "Syntax error " when trying to define a class Like Class myClass Public height Public ID Private t_width
1
by: Ghanashyam | last post by:
Dotnet Framework 1.1 is installed on my computer running on Windows 2000 Professional.Visual Studio >net 2003 is installed.When I try to create an ASP.NET project I get following error. The web...
5
by: snicks | last post by:
I'm trying to exec a program external to my ASP.NET app using the following code. The external app is a VB.NET application. Dim sPPTOut As String sPPTOut = MDEPDirStr + sID + ".ppt" Dim p As...
3
by: holysmokes99 | last post by:
I get an error when I try to execute the following code using ADO.Net in VB.Net: conn.open sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'" command=new SqlCommand(sqlString)...
2
by: ticars | last post by:
I'm getting a weird error when trying to access a user control from within a base page during runtime. Here's what I have: I have a master page with a user control on it. I then have a few...
3
by: LRI41 | last post by:
I googled an error message and it came up with two posts on your forum and when I got there it said I needed to register to post my question, I am not sure whether is this the correct place to...
1
by: praom2104 | last post by:
i m newbie to linux......wen ever i create database it returns an error as "mysql> create databases new; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.