How to combine text Fields in Base

[Nirliq]

Hello All,
I am changing from MS to base as I am trying to find a way to combine text in a query or report. I would like to be able to:

1. If there is no other person then
   just Combine Last Name and First
   Name. i.e Smith Bob

2. If there is a spouse with the same last name then Combine Last Name and First Name and First Name i.e. Smith
   Bob & Brenda Or

3. If there are two people with different names combine them all. i.e Smith Bob & Jones Marsha.

In MS I can have a calculated field with:
[01 Last Name] & " " & [01 First Name] & " & "+([02 Last Name] & " ")+([02 First Name])

In Calc used a very complicated Nested if statement :
=IF(D3="",C3,IF(C3=“Info Not”,“Info Not Available”,IF(F3<>"",+D3&", “&C3&” “&” & “&” “&F3&”, “&E3,IF(E3<>”",+D3&", “&” “&C3&” & “&E3,+D3&”, "&C3))))

I hope that I am slightly more clear than mud in my question and thank you all for any help you can send me.

Nirliq

Thank you, please find my sample attached. Directory - Copy.odb

[Ratslinger]

Hello,

Sorry but there is no way to answer this without more information. What database are you using (Base is not the database but the front end to the database)? This is most likley HSQLDB embedded or Firebird embedded. SQL (queries) can differ with each database. Joining items can be with concat or || and possibly other means depending on this.

Your scenarios are not clear at all. Based upon what is stated, if you have say 1000 records most will have different names. Combine them all? Need clarity. And there is no structure presented. What about record layouts, field types and possibly more; can’t suggest SQL without this information. It would be best to provide a sample Base file (no personal or confidential information).

[Nirliq]

First of all, thank you for getting back to me. the database would be the Default HSQLDB
I have 194 Records. I would be happy to send you a sample database can you tell me how to attach a copy?
Thank you

[Ratslinger]

@Nirliq,

Edit your original question. Then in upper left is a toolbar. Select Paperclip icon to add an attachment. See → How do I attach a file to my question/answer?

[Ratslinger]

Hello,

Have done this two ways - via a case statement and using Union with Select statements.

Case is better and the multiple Unions require the SQL to have Run SQL command directly turned on (toggle of icon or menu - Edit).

Returned sample ------ NameConcat.odb

Note - Your field naming is not consistent. Did not modify your names.

HSQLDB embedded is old and being replaced by Firebird embedded (someday). There are other choices such as HSQLDB split and more.

As with all questions, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[Ratslinger]

The solution provided does not check for all conditions. For example, you could have a stray space in a name field being checked & that would not be NULL.

[Nirliq]

Actually, the solution provided me with the path that is needed to complete tasks.
Good job.