Need help with concating a sql select in vba code

[jker]

I have the folloing sql CODE BUT THE DOUBLE QUOTES ARE GIVING ME A PROBLEM when rewrite into VBA

Sql is: SELECT F1 &"x" & F2 FROM XTABLE WILL GIVE ME A RESULT OF F1xF2

Rewrite it into VBA format as :

DIM mySQL1 as STRING
mySQL1 = "select f1 &"x"& f2 from xtable"


I get compile error on the select statement because of the double quotes.

QUESTION: how do i compose the sql statement into a VBA acceptable format and getting the same result??

[xps35]

I am not sure what you want to do with the sql, but try:

mySQL1 = "SELECT f1 & 'x' & f2 FROM xtable"

[moke123]

What's the result supposed to look like?
Datatypes? What's f1 and f2?

mySQL1 = "SELECT """ & f1 & "x" & f2 & """ as SomeAlias FROM xtable"

[NoellaG]

as this is the SQL server section: note that the concatenation sign in SQL server is +
T-SQL statement:

select [field1] + ' x ' + [field2]

If one of the fields is not a (n)varchar you need to convert it to (n)varchar, for example using the cast function:

select cast([field1] as varchar(50))

[June7]

If you want Access VBA to build SQL to open a recordset of a linked SQLServer table, either + or & should work for concatenation, although the results can be different depending on field content. If a field is Null, the result with + concatenation will be Null.

[NoellaG]

If a field is Null, the result with + concatenation will be Null.

not always true, it depends on the database settings:

[June7]

I am not aware of any such property setting in Access. I have linked SQLServer tables and use of + concatenation returns Null when input is Null. Concatenation with & does not. Same as with a local table.

This is because + is also arithmetic operator and arithmetic with Null returns Null. This carries over when used for concatenation.

A pass-through query would possibly behave differently - I will have to try it.