SOLVED

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

Copper Contributor

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/, /Forenames, i.e. 

 

Surname only

Surname, Forenames

Forenames only

 

The second to report mobile or landline if absent.

 

I want to concatenate the 5 address fields and at best I get #Error datum and a handful of single fields out of the 5.  

 

SELECT
IIF([Contacts & Preferences].[Surname] is null,
   IIF([Contacts & Preferences].[Forenames] is null, "", [Contacts & Preferences].[Forenames]),
   IIF([Contacts & Preferences].[Forenames] is null, [Contacts & Preferences].[Surname], [Contacts & Preferences].[Surname]+", "+[Contacts & Preferences].[Forenames])),
IIF([Contacts & Preferences].[AddrList Mobile] is null,
  IIF([Contacts & Preferences].[AddrList Land Line] is null, "", [Contacts & Preferences].[AddrList Land Line]),
  [Contacts & Preferences].[AddrList Mobile]),
[Contacts & Preferences].[AddrList Address Line 1],
[Contacts & Preferences].[AddrList Street No],
[Contacts & Preferences].[AddrList Street],
[Contacts & Preferences].[AddrList Address Line 3],
[Contacts & Preferences].[AddrList Village/Town],

[Contacts & Preferences].[AddrList Postcode]
FROM
[Contacts & Preferences]
WHERE
[Contacts & Preferences].[AddrList Street] is not null
ORDER BY
1
;

 

Should I be getting a Dummies Guide to Visual Basic?

 

Happy to go private on this and all that implies.

 

Martin

3 Replies
best response confirmed by Klax0 (Copper Contributor)
Solution

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.12

 

and otherwise ask in forums. ;)

 

Servus
Karl
****************

Access Bug Trackers

Access News
Access DevCon

1 best response

Accepted Solutions
best response confirmed by Klax0 (Copper Contributor)
Solution

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

 

View solution in original post