[Solved] Concatenate 2 fields with a space

[evwool]

I want to use a text box in my report to join the FirstName and Surname fields with the space between them (FirstName space Surname) ie the box should show John Smith.
I've put in a text box, formatted it as Text, next to Data Field Type in the Properties box I've chosen Field Or Formula.
but what do I type next to Data Field Property?
[FirstName] & [Surname] gives me JohnSmith
[RSName] & " " & [RFName] also gives me JohnSmith because it reverts back to [FirstName] & [Surname] when I switch from Design View to Open the report.
Any ideas?
Before you ask, there is a reason why I'm not doing this in the query on which the report is based but its another issue.

[Villeroy]

Theoretically, any query should work with a report exactly like a table does.

[FirstName] & [Surname] gives me JohnSmith

Obviously

[RSName] & " " & [RFName]

How about a space in single quotes? Sorry, I can't tell for sure. Until now I never used the report builder extension.

[evwool]

Thanks for trying Villeroy but no dice.
In fact, neither does
[RSName] & 'A' & [RFName]
work. It just gives me JohnSmith rather than JohnASmith
Although I can use
[RSName] & "A" & [RFName]
to give me JohnASmith
So the double quote seems to be necessary. Now I just need a way to make it work with a space.
Any more ideas?

[RPG]

Hello

Make a view of the names and use that in a report

Romke

Code: Select all

drop view "Toon_Namen"
create view "Toon_Namen"
("NAAMID","Naam","schr_naamvol","schr_vnaam","roepnaam","voorletters","tussen","Titel","achter","m_v","overleden","geboren")
AS 
SELECT
"NAAMID",
COALESCE("N"."Titel" || ' ' ,'') || COALESCE("N"."schr_vnaam" || ' ','')
|| COALESCE("N"."tussen" || ' ','') || COALESCE("N"."schr_naamvol" || ' ','') 
|| COALESCE ("N"."achter" || ' ','')
 as "Naam" ,
"schr_naamvol","schr_vnaam","roepnaam",
"voorletters","tussen","Titel","achter","m_v",
"overleden","geboren"
FROM "namen31" as "N"
;)

[evwool]

Thanks for giving it a go RPG. I was hoping to avoid using SQL if I could and just use a report textbox if it's possible but perhaps it isn't.

Your SQL is too complex for me to be able to adapt it. Would you be able to simplify it, omitting the CREATE VIEW part (since I can do that physically), and removing the extra fields so that it only contains the minimum necessary?

The report is based on a View since it has 2 tables joined using a Right Join (so that I can get all the data from the 'lookup' table even if the record doesn't appear in the second one).
I had thought that Views were unable to cope with calculated fields (but then I had only attempted them using the user interface).

[evwool]

OK, I put the calculated field
"TblRep"."RFName" || ' ' || "TblRep"."RSName" AS "RName"
into my View's SQL using Direct SQL and it showed in the View
but when I try to create a report based on my view, the RName field does not appear

[RPG]

Hallo

I did copy the complete view there I did make it in the time before it was easy to do in the GUI. Now you can do it (more) easy in the GUI.

The most important part is

Code: Select all

COALESCE("FirstName" || ' ' ,'') ' This is where it happens

When Firstname is empty then comes a zero length string and not an empty string.
When it contain a value then it get the value with a space.
For the second test you need only a zero length string.

This assumes that I have a empty value when there is no name and not a zero length string.

I think in your case you get

Code: Select all

COALESCE("FirstNamel" || ' ' ,'') || COALESCE("Surname" ,'') as "CompleteName"

There is maybe no reason for making a view or separate query but that is to you.

Romke

[evwool]

Now I see what you mean, RPG. The COALESCE function made all the difference. I see now that the reason that the View didn't produce a report was because of those empty fields. (because of the right join, some of the numbers had no names attached to them). I just assumed you could not produce reports from Views with calculated fields. I see now that what Report Builder can't cope with, is calculated fields where the result is an empty field. When I used the COALESCE function to replace my Empty fields with zero-length strings, the field showed up in Report Builder.
Thank you RPG. Solved!

[jeff.melick]

You might hate this, but I made it work by adding a new column titled "Separator," then simply typed ", " in that column (no quotes). I wanted it to be LAST, FIRST.

Therefore, for each row, you'd simply need to use Fill Down to insert the comma space

Insert that string into your Concatenate function, and it will add the space and comma. Leave the comma out if you just want the space.