Hi
I need to concatentate 2 text fields and show the result in a 3rd created field.

I can do this fine within Access using -

3rdFieldName: [Field1Name] & [Field2Name]
but I get a syntax error when I try using an Excel ODBC Query

The best I was able to manage using an Excel ODBC Query was to type the following into the SQL editor -

SELECT [tableName.Field1] & [tableName.Field2] AS [3rdFieldName] FROM Datasource

This returns just the concatenated fields (OK) but I can't add any of the other (normal) fields.

Katherine

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`

Thanks Bill but I'm still stuck

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

Sorry couldn't figure out how to attach a document, so here's the SQL from the query which returns the 2 columns I need to concatenate. I would call the calulated colum Address6.


SELECT tblTenant.CorrespondenceAddress4, tblTenant.CorrespondenceAddress5
FROM `P:\PropertyDatabase\ProcessProperty_KM`.tblTenant tblTenant

SELECT tblTenant.CorrespondenceAddress4 & ' ' & tblTenant.CorrespondenceAddress5 AS 'Address6'
FROM `P:\PropertyDatabase\ProcessProperty_KM`.tblTenant

This format worked. Did get an alert that the query can not be displayed graphically, but the query works fine.

Thank very much Bill. That worked perfectly.
Katherine