Concatenation in SQL String

[DEI]

I have written an SQL String for a crosstab query. I want to format the
column heading as "Year, Qtr, Quarter" (Ex: "2005, Qtr 3"). I am trying to
do this with the Pivot statement on the SL string, but can not get the syntax
correct, and have tried everything. Do I need to use double quotes?

Here is the statement. This is driving me crazy, I wish I fully understood
the logic. I get a syntax error when this line is evaluated. What is the
rule for a text string inside a string?

"PIVOT [CONTROL].[Y]" & ", Qtr" & "[CONTROL].[Q];"

Thanks in advance

 

[DuckHero]

"DEI" skrev:

I have written an SQL String for a crosstab query. I want to format the
column heading as "Year, Qtr, Quarter" (Ex: "2005, Qtr 3"). I am trying to
do this with the Pivot statement on the SL string, but can not get the syntax
correct, and have tried everything. Do I need to use double quotes?

Here is the statement. This is driving me crazy, I wish I fully understood
the logic. I get a syntax error when this line is evaluated. What is the
rule for a text string inside a string?

"PIVOT [CONTROL].[Y]" & ", Qtr" & "[CONTROL].[Q];"

Thanks in advance

I think you just need to remove the " around the controls, so your line
looks like this:
PIVOT [CONTROL].[Y] & ", Qtr" & [CONTROL].[Q]
(haven't worked much with pivot's - so I can't remember how you qualify the
names of the controls)

Generally speaking when you concatenate strings, you only put the "'s around
the bits of text that are static (i.e. do not rely on dynamic data from
fields, controls, properties and so on). E.g to concatenate the value of a
text box into a greeting, you would go:

Dim sGreeting as String
sGreeting = "Hello " & tbxUsername
msgbox sGreeting

Hope this helps

 

[DEI]

Thanks for the response, but that did not work. The SQL string is in VBA,
not the querydef in the database. That would work in the SQL view for the
query object.

"DEI" skrev:

I have written an SQL String for a crosstab query. I want to format the
column heading as "Year, Qtr, Quarter" (Ex: "2005, Qtr 3"). I am trying to
do this with the Pivot statement on the SL string, but can not get the syntax
correct, and have tried everything. Do I need to use double quotes?

Here is the statement. This is driving me crazy, I wish I fully understood
the logic. I get a syntax error when this line is evaluated. What is the
rule for a text string inside a string?

"PIVOT [CONTROL].[Y]" & ", Qtr" & "[CONTROL].[Q];"

Thanks in advance

I think you just need to remove the " around the controls, so your line
looks like this:
PIVOT [CONTROL].[Y] & ", Qtr" & [CONTROL].[Q]
(haven't worked much with pivot's - so I can't remember how you qualify the
names of the controls)

Generally speaking when you concatenate strings, you only put the "'s around
the bits of text that are static (i.e. do not rely on dynamic data from
fields, controls, properties and so on). E.g to concatenate the value of a
text box into a greeting, you would go:

Dim sGreeting as String
sGreeting = "Hello " & tbxUsername
msgbox sGreeting

Hope this helps

 

[DEI]

I figured out the syntax. It is:

"PIVOT [CONTROL].[Y] & "", Qtr " & """ & [CONTROL].[Q];"

No single qoutes. What you are seeing are all double qoutes. What a pain.
I used the immediate window (debug.print SQL) to see how the string was
really being evaluated.

Thanks for the response, but that did not work. The SQL string is in VBA,
not the querydef in the database. That would work in the SQL view for the
query object.

"DEI" skrev:

I have written an SQL String for a crosstab query. I want to format the
column heading as "Year, Qtr, Quarter" (Ex: "2005, Qtr 3"). I am trying to
do this with the Pivot statement on the SL string, but can not get the syntax
correct, and have tried everything. Do I need to use double quotes?

Here is the statement. This is driving me crazy, I wish I fully understood
the logic. I get a syntax error when this line is evaluated. What is the
rule for a text string inside a string?

"PIVOT [CONTROL].[Y]" & ", Qtr" & "[CONTROL].[Q];"

Thanks in advance

I think you just need to remove the " around the controls, so your line
looks like this:
PIVOT [CONTROL].[Y] & ", Qtr" & [CONTROL].[Q]
(haven't worked much with pivot's - so I can't remember how you qualify the
names of the controls)

Generally speaking when you concatenate strings, you only put the "'s around
the bits of text that are static (i.e. do not rely on dynamic data from
fields, controls, properties and so on). E.g to concatenate the value of a
text box into a greeting, you would go:

Dim sGreeting as String
sGreeting = "Hello " & tbxUsername
msgbox sGreeting

Hope this helps