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/, /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