Extraneous character in Concatenation

[bruegel]

I have a simple concatenation in a query listing writers of projects e.g. Writer 2 Name: [Writer 2 LN] & ", " & [Writer 2 FN 2]

which takes Albert and Hall to give me Hall, Albert. However if there is only a single writer and no writer 2, 3 etc. then I get a field with no name but still with the comma. I'm trying to find a way of saying 'if there is no Writer 2 then leave the field completely empty' but I'm tying myself into knots and writing longer and longer statements. I feel there should be a simple way to do this but darned if I can find it.

Solution gratefully sought

Pieter

[recyan]

Have you tried something like below :

IIf(IsNull([Writer 2 FN2]),[Writer 2 FN],[Writer 2 FN] & ", " & [Writer 2 FN2]) AS [Writer 2 Name]

Thanks

[bruegel]

I've been playing around with similar if maybe not exactly the same - got invalid syntax for your suggestion I'm afraid

Pieter

[pbaldy]

Try this:

[Writer 2 LN] & (", " + [Writer 2 FN 2])

[bruegel]

Still getting that single comma in otherwise empty field unfortunately

Pieter

[pbaldy]

That means the field isn't necessarily Null. I don't think there was anything wrong with recyan's formula, but it was in SQL view where what you posted was design view. Try this:


[Writer 2 Name]: IIf(Nz([Writer 2 FN2], "") = "",[Writer 2 FN],[Writer 2 FN] & ", " & [Writer 2 FN2])

The spaces in your names aren't worth the trouble in the long run, and it appears you have a normalization problem with the multiple fields for the same type of value.

[bruegel]

I am going to close this thread as I realise that I made a typo in my original post. Thanks for help and fulsome apologies for my carelessness. Will play around with solutions but may repost if I am getting nowhere and hope for your tolerance. Many thanks. Pieter

[pbaldy]

No problem Pieter.

[bruegel]

I solved this by using a combination of the two answers, using an IsNull function but closer to the syntax of pbaldy's solution. Because of my typo I don't think it would help to post the sql here. If someone has the same dilemma they can contact me direct.

Regarding the spaces. Unfortunately the data was imported from a very large spreadheet that didn't use databse field name conventions and had to be exported again to a similarly ill named spreadsheet. If I had constructed the db from scratch I would have observed conventions but renaming the whole thing twice was just too much like hard (and utimately unrewarding) work.

Thanks for the help

Pieter