Forum Discussion
What I could do in Oracle, MySQL, SQL Server I cannot do in Access Jet SQL: Concatenate.
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
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
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
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.12and otherwise ask in forums. 😉
Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon