More than one middle name

[vorstopzolder]

I have a question about middle names. I don't know if I'm searching with the wrong keywords but I can't find an answer. I have a table with the next fields: surname, middlenames and name. I made a report where I combine all three of them like this:

 
=([name] & (" "+[middlenames]) & " " & [surname])

It works perfectly with names like "Celia Cruz" or "Jack the Ripper". But it doesn't work with names with more than one middle name like "Celia de la Cruz", because the result in my report will be: "Celia de Cruz". The problem is that the expressions checks for white spaces en cuts the string if it finds one.

Does somebody know what I can do about it?
Thanks in advance.

[ajetrumpet]

you can use a function, or you might use an IIF statement in the source of the box.

if this is a field that is displaying one per record on a report, IIF might be better than a function call, because it is instantaneous where a function is not.

[vorstopzolder]

Good advise! This is my solution and it works:

=([name] & (" "+(IIf([middlenames]=Null;Null;[middlenames]))) & " " & [surname])

I don't know exactly why it works because if middlenames is not null and the middle name is "de la", there is still a white space?!? But it works. That's great.

Thank you

[thhui]

The correct one should be refined as

=([name] & ((IIf([middlenames]=Null;Null;" "+[middlenames]))) & " " & [surname])

[vorstopzolder]

Thanks, makes more sense indeed. Is it possible that this expression doesn't work if you put more records of the same table in one report?

I use this file on three different computers (Access 2007 and 2003). I made this DB at home (Access 2007) and it's saved as a 2003 file. But if a use it on my computer at work (also 2007) I have to change ";" to "," in the IIF expression. If not, I get an error. And this is just one example (there's more that works differently). Any idea why?

[thhui]

By the way, the syntax of separator of iif is "," instead of ";"
It just happens that it does not warn for error.