Create Crosstab in Code

[Rjc8513]

Okay, this is driving me crazy! I am trying to create a crosstab query using code. I keep getting the 'Syntax Error' message when trying to run this code:

dbs.CreateQueryDef "cqry- VOLUME", _
"TRANSFORM Sum([tbl VOLUME].[ACTL) AS [The Value]" _
& " SELECT [FINANCE NO], [OFFICE], [LINE]" _
& " FROM [tbl VOLUME] PIVOT [WEEK];"

Thanks for the help.

Richard...

 

[jimmythegeek]

Try this, I think you got the line continuation characters (& _) backwards.

dim strSQL
strSQL = "TRANSFORM Sum([tbl VOLUME].[ACTL) AS [The Value] " & _
"SELECT [FINANCE NO], [OFFICE], [LINE] " & _
"FROM [tbl VOLUME] PIVOT [WEEK];"

dbs.CreateQueryDef("cqry- VOLUME", strSQL)

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[Rjc8513]

Thanks for the response but, it still didn't work. I'm still getting a syntax error. Below is my code altered to reflect your suggestions:

dim strSQL
strSQL = "TRANSFORM Sum([tbl VOLUME].[ACTL) AS [The Value] " & _
"SELECT [FINANCE NO], [OFFICE], [LINE] " & _
"FROM [tbl VOLUME] PIVOT [WEEK];"

dbs.CreateQueryDef "cqry- VOLUME", strSQL

Thanks.

Richard...

 

[SgtJarrow]

If this is a copy/paste, your error is here.....

strSQL = "TRANSFORM Sum([tbl VOLUME].[ACTL]) AS [The Value] " & _
"SELECT [FINANCE NO], [OFFICE], [LINE] " & _
"FROM [tbl VOLUME] PIVOT [WEEK];"

You were missing an end bracket.....also, it does not matter if you use & _ or _ &.....I personally prefer to use _ & because you can easily see that the line that starts with a & is continuation of the previous line....

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Rjc8513]

Thanks Robert, but if I add that end bracket I then get an error message citing the TRANSFORM statement.

Without the end bracket I the error:
'Syntax error in query expression 'Sum([tbl VOLUME].[ACTL)...' .

With the end bracket added I get the error:
'Syntax error in 'TRANSFORM' statement.'

Richard...

 

[SgtJarrow]

That might be correct......

But if you get an error either way.....it will never be right without that end bracket....the end bracket is ending the name of a field....and if you don't supply it, the field is never ended....

Once you add that bracket, the error is now telling you there is a problem with the Transform....something totally different..... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[raskew]

The underlying syntax error results becauset your code is missing a GROUP BY clause, necessary when using the TRANSFORM statement.

 

[Rjc8513]

raskew,

that did the trick! thanks for the help.

thanks to all others, too.

Richard...