What I could do in Oracle, MySQL, SQL Server I cannot do in Access Jet SQL: Concatenate.

I just want to concatenate address fields in a report. It seems that I have to do all this IFF... null business: The first IIF is the only way to report all the combinations of Surname/, /For...

access

developer

Karl_Donaubauer
Jun 29, 2023
Hi,

How about these expressions?

[Surname] & IIf([Surname]+[Forenames]>"", ", ") & [Forenames]

Nz([AddrList Mobile], [AddrList Land Line])

Explanation: & is the standard concatenation operator in Access and JET SQL. With + you get NULL if one of the parts is NULL. Therefore the check for your separator. Nz() is an Access function that gives back the first expression or if the first one is NULL, the second one.

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

Klax0

Copper Contributor

Jun 29, 2023

Karl_Donaubauer First class Karl. Erste Klasse! Toll! Ich habe in Deutschland gewohnt. Is there a book you would recommend. I'll use the same technique on the address fields.

The very best regards,

Martin

Karl_Donaubauer

MVP

Jun 29, 2023

Hi Martin,

I haven't looked at any Access books in ages. I think there are hardly any left. So I better hold back with recommendations.

For such simple or practical things and since you know German, you can first look at my FAQ pages, e.g.

Leerzeichen in kombiniertem Feld vermeiden
https://www.donkarl.com/?FAQ2.12

and otherwise ask in forums. 😉

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon