Tried it with SQL query Editor and with ADO using Excel VB and didn't have any trouble. However you have to be careful to use the correct syntax. It looks like you have the wrong syntax in your SQL String where you enclosed the Tablename and the Fieldname in the brackets. You should separate the Table name and field names. Use the Query builder in Access or the wizard in Query Editor to get an initial string with the correct syntax.
Adapted from Access Editor
strSQL = "SELECT [Field One], [Field Two] & [Field Three] AS ConcatFld" & _
" FROM TableName"
SQL Query Editor
SELECT Table.`Fld One`, Table.FieldTwo & Table.FldThree AS ConcatFld
FROM Table Table
ORDER BY Table.`Fld One`
I've attached a screen shot of what I'm trying to do.
I need to display the 2 addresses in the example AND a third new column which will be the result of concatenating them in the same query.
Kind regards, Katherine