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 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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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
|
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...
|
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...
|
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |