Results 1 to 2 of 2
2012-08-01, 13:29 #1
- Join Date
- Jan 2001
- San Francisco, California, USA
- Thanked 0 Times in 0 Posts
MS Query in Excel 2010: concatenating two fields returns error?
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
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
(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.
Last edited by Cecilia; 2012-08-01 at 15:33.
2012-08-02, 04:06 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- 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
Microsoft MVP - Excel