Concatenation include or exclude an "&"

[Rogeman]

Hello,


I am trying to concatenate the following fields :- Title, Initials, Forename_Male, Forename_Female, Surname
I am having trouble including or excluding the "&" between the two Forename fields, which needs to be used when there is a couple of members.
When there is only a single Male or Female I need that "&" to not be visible.
How can I achieve this in my query that I am using to then display this data in a Report?


This is how I would like the data to appear.
Mr & Mrs Patrick & Anne Smith


Mrs Anne Adams
Mr Barry Thompson


This is what I have displayed.
Mr & Mrs Patrick & Christine Smith
Mrs &Anne Adams
Mr Barry& Thompson


Below is the code that shows my second set of results.
ALLnames: [MEMBERSHIP].[title] & " " & [forename_Male]"&" & [forename_Female] & " " & [surname]




Roger

[Welshgasman]

You could use the IIF() function or write your own, as it looks like your DB is not normalised?

[JoeM]

Maybe something like this:

ALLnames: [MEMBERSHIP].[title] & " " & IIF(([forename_Male]<>"") AND ([forename_Female]<>""),[forename_Male] & " & " & [forename_Female],[forename_Male] & [forename_Female]) & " " & [surname]

[Rogeman]

Thank you for your reply, I used one of my older Databases to 'normalise' (I had never done this before) It changed a lot of the tables etc...I have now lost some field details.
I am by no means an experienced programmer and do not quite understand normalising. The answer below from JoeM does exactly what I needed so I will continue with that for the present and when I have time will try to normalise another old database (I continually make backups as I realise that it is too easy to ruin a current Database) to see what happens and try to understand it better.

Thanks again
Roger

[Rogeman]

JoeM,

Thanks for your reply, it works exactly as I needed, I am just trying to understand How it works so I know for next time.

Thanks again much appreciated.

Roger

[JoeM]

The formula is really broken into three parts, the Title, the Forename, and the Surname.

TITLE: [MEMBERSHIP].[title] & " " &
FORENAMES: IIF(([forename_Male]<>"") AND ([forename_Female]<>""),[forename_Male] & " & " & [forename_Female],[forename_Male] & [forename_Female]) & " " &
SURNAME: [surname]

The Title and Surname parts are pretty straightforward (direct text). It is the forename part that has the IIF formula.

Basically, what that IIF formula does is to check to see if BOTH the Male and Female forenames are NOT blank.
If that is the case, then join both values with an " & " in between.
If that is NOT true (meaning one is blank), then join them WIHTOUT an " & " in between (as you will just be joining a field value with an empty field).

Does that help clarify things, or do you still have any questions?

[Rogeman]

JoeM,

Thanks, you make it look so simple with your explanation equally clear and concise.
Books and 'Utube' only cover joining two fields mainly.
My Database has to include Husband and Wife in one row, the TITLE field always has ;&; to join them.
It does become more complicated when they are 'Partners' each with their own forename/surname.(your answer still works then though).
This is what it produces:- (Mr & Miss Ken & Sharon smith & Jones).
If I join Ken with Smith, and Sharon with Jones it would be confusing, there is only one instance of this so I will ignore it.

I now need to start a new thread on 'Normalising', something I never knew I needed to do!

Thanks again
Roger