Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Query in Excel 2010: concatenating two fields returns error?

    Hi All,

    I'm having issues with MS Query in Excel 2010 and I'm trying to figure out if this is a driver or some other problem. The data source is DB2. Before we downgraded to Office 2010 everything was hunky dory in WinXP/Office 2003. Has anyone else had a problem that sounds like this, and if so, what did they do to resolve it?

    The following query works great in MS Access 2010:

    SELECT LOCN_PHY_POST_CDE, LOCN_PHY_POST_EXT, [LOCN_PHY_POST_CDE] & IIf([LOCN_PHY_POST_EXT]='0000','',' - ' & [LOCN_PHY_POST_EXT]) AS ZIP4
    FROM MYDB;

    but I can't seem to make the equivalent work in MS Query/Excel 2010. I get a lot of "Cannot display graphically" problems followed by other errors. First, the brackets generated the error: Incorrect Column Expression: '['
    So I simplified it down to the following:

    SELECT LOCN_PHY_POST_CDE, LOCN_PHY_POST_EXT, LOCN_PHY_POST_CDE & LOCN_PHY_POST_EXT
    FROM MYDB

    (For the record, I also tried using + with no change in outcome.)

    and now my error is something along the lines of "Did not expect LOCN_PHY_POST_CDE following Select statement."

    Well if MS Query doesn't expect a field name following a Select statement, then certainly I don't know how to talk to it.

    Any help or pointers or suggestions appreciated. Even a "it's a driver thing" would help, but even better would be some proof of such to send to the IT guys who don't believe me that anything's wrong.

    TIA!
    Last edited by Cecilia; 2012-08-01 at 15:33.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming MYDB is a table name, try prefixing each field with the table name.
    SELECT MYDB.LOCN_PHY_POST_CDE, MYDB.LOCN_PHY_POST_EXT, MYDB.LOCN_PHY_POST_CDE & MYDB.LOCN_PHY_POST_EXT
    FROM MYDB
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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