Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Question Unanswered: Concatenating Access Fields with Excel Query

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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`
    ~

    Bill

  3. #3
    Join Date
    Aug 2006
    Posts
    4

    Still Stuck

    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

  4. #4
    Join Date
    Aug 2006
    Posts
    4

    Sorry no Screen shot

    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

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  6. #6
    Join Date
    Aug 2006
    Posts
    4

    Thank you

    Thank very much Bill. That worked perfectly.
    Katherine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •