Excel 2010 & ms query...concatenate two fields?

[Cecilia]

Hi All!

I have what appears to be a simple query, only MS Query doesn't seem to like it too much. I'm trying to query a DB2 data source. I could do it just fine in Excel 2003 but we've "downgraded" to 2010 and now simple stuff doesn't work.

The following seems to generate a syntax error. Can someone help me?

([LOCN_PHY_POST_CDE] & If(isnull([LOCN_PHY_POST_EXT]),,'-' & [LOCN_PHY_POST_EXT]) as ZIP4

I've even tried this and get a syntax error :(

[LOCN_PHY_POST_CDE] & [LOCN_PHY_POST_EXT]

Is there something funky about "&" in Ms Query 2010? Jet?

TIA!

[HansV]

Try changing

Code: Select all

If(isnull([LOCN_PHY_POST_EXT]),,'-' & [LOCN_PHY_POST_EXT]) 

to

Code: Select all

IIf(IsNull([LOCN_PHY_POST_EXT]),'','-' & [LOCN_PHY_POST_EXT]) 

This works for me with Access; I can't test with DB2.

[Cecilia]

Thanks Hans, but unfortunately this has to be in Excel :(

I'd settle for being able to do this: [LOCN_PHY_POST_CDE] & [LOCN_PHY_POST_EXT]
but even something simple like this generates an error, which is why I think something changed in 2010.

[HansV]

I did mean MS Query in Excel but with an Access database as record source. I cannot test with a DB2 database as record source.
What happens if you use + instead of & ?

[Cecilia]

+ generates an error too.

MS query doesn't like the [ ] either. I'm guessing this is a jet problem? grrrr.

This is the exact error: incorrect column expression: '['

It does work quite perfectly in Access.

[HansV]

Sorry, since I don't have access to a DB2 database I can't help you with this.

[Cecilia]

No prob, Hans, I appreciate the attempt :)

[HansV]

This is the exact error: incorrect column expression: '['.

What if you omit the square brackets:

LOCN_PHY_POST_CDE & LOCN_PHY_POST_EXT

or

LOCN_PHY_POST_CDE + LOCN_PHY_POST_EXT

[Cecilia]

Neither of those options works. I'm fairly certain at this point it's some sort of driver or jet issue, I just need to figure out which one, 'cause IT doesn't believe there is a problem (as usual).

[kdock]

The following seems to generate a syntax error. Can someone help me?

([LOCN_PHY_POST_CDE] & If(isnull([LOCN_PHY_POST_EXT]),,'-' & [LOCN_PHY_POST_EXT]) as ZIP4

This may be a stupid question (as I'm not all that familiar with MS Query), but is there a missing end paren in the "If(isnull([LOCN_PHY_POST_EXT])),, " (and so on...)?

Just a thought...?
Kim

[Cecilia]

Hi Kim,

No such thing as a stupid question. But right now I'm just trying to get LOCN_PHY_POST_CDE & LOCN_PHY_POST_EXT to work, and it has no paren or brackets, or anything else. :-/

Thanks :)