Unwanted spaces when concatenating fields

[Eddy]

I am trying to concatenate the contents of the following fields:

[FirstNames]
[Prefix]
[LastName]

Company names are used in the LastName field.

There should be only one space between the first two and the last two fields.

I have the following expression in a query field:

CombiName: IIf([FirstNames] Is Null,[LastName],IIf([Prefix] Is Not Null,[ FirstNames] & " " & [Prefix] & " " & [LastName],[Firstnames] & " " & [LastName]))

When running the query the expression works correctly except in some of the cases where the FirstName and LastName appear. Sometimes there is just one space (which is correct and to be expected) between the FirstNames and LastName but sometimes there are, to me inexplicably, two spaces.
I cannot find a pattern in this, it seems to me that 2 spaces are randomnly attached.

I tried using the TRIM function just in case the fields have any additional spaces:

CombiName: IIf([Firstnames] Is Null,[LastName],IIf([Prefix] Is Not Null,[Firstnames] & " " & [Prefix] & " " & [LastName],TRIM([Firstnames]) & " " & TRIM([LastName])))



But that did not resolve the problem.

Am I overlooking something or is there something wrong with the expression?

[ssanfu]

Welcome to the forum.

This site should help you.
http://windowssecrets.com/forums/sho...-concatenation

[CJ_London]

I suspect that prefix is not null but a zero length string, so you are looking at the wrong bit of code

two potential fixes

1. replace '[Prefix] Is Not Null' with 'nz([Prefix],"")<>""'
2. replace double spaces with single space on the whole calculation replace(IIf([FirstNames] Is Null,[LastName.....)," "," ")

[rpeare]

CombiName: IIf(len(trim([Firstnames])) = 0,trim([LastName]), IIf(len(trim([Prefix]))= 0, trim([Firstnames]) & " " & trim([Prefix]) & " " & trim([LastName]),TRIM([Firstnames]) & " " & TRIM([LastName])))

as already pointed out you may have characters that will invalidate a 'null' check but be otherwise invisible so check for a zero length string instead of null

[Eddy]

Thanks for your tip ssanfu

I tried the much easier following expression:

CombiName: ([FirstNames] + ” “) & ([Prefix] + “ “) & [LastName]

But I am getting the same result.
I am however grateful for learning this easier expression.

Thanks also Ajax. The table on which the query is based on was created by someone else. Only the [LastName] field was set to allow zero length string. I changed it to ‘no’.
Your first suggestion for a potential fix created an error, but when I added apostrophes to the quotation marks it accepted the expression. But running the query in this fashion gave me the same undesired result. I also tried your second suggestion but the result was the same.

Last but not least thanks also rpeare for trying to trim down the spaces. I copied your alternative, but it also did not change the result.

I don’t understand the cause of this peculiar outcome. And the extra spaces only randomly happens when the [FirstName] and [LastName] is shown. I have concatenated fields in the past but never had this kind of a problem.

Thanks again, I am so very grateful for all of your suggestions.

[rpeare]

CombiName: trim([FirstName]) & " " & Trim([Prefix]) & " " & Trim([LastName])

[Eddy]

Hi rpeare,
Using the last suggestion now guarantees that there are twospaces when [FirstName] and [LastName] is shown, not the desired one space. Sothe random presentation of one and two spaces is gone.
Additionally when only the [LastName] has to be shown (in the case of acompany) two spaces are shown in front of [LastName]. This I would expect.
When the [LastName], [Prefix] and [LastName] is shown it isperfect, as to be expected.
It still remains a mystery why the prior expressions thatyou all suggested did not work.
Thanks anyway for trying to help.

[Eddy]

Another strange thing that I’m noticing is that when thequery is run, inexplicably only the [FirstName] is shown in a few records,while both the [FirstName] and [LastName] are filled with text. Very strange.
I do not understand why the result is so bizarre while allthe expressions that were presented seem to be sound.

[rpeare]

CombiName: trim(replace(trim([FirstName]) & " " & Trim([Prefix]) & " " & Trim([LastName]), " ", " "))

in that replace statement the first one should be a double space not a single space, this website pared out the second space

[rpeare]

Are you importing this data from an external source? if you are there may be some special characters buried in the text or some formatting strings that are hosing things.

[Eddy]

Hi rpeare,
At last a huge progress, it works!!!! You are awesome!!!
I did import the table from another database that someone elsecreated. When I go into the text of all three fields I do not see any strangecharacters in them. The datatype of all three fields is text.
The only problem that is now left is that inexplicably onlythe [FirstName] is shown in a few records, while both the [FirstName] and[LastName] are filled with text. Very strange, but there aren’t many recordswith this outcome. I’ll struggle to see if I can figure that one out.
Thanks so much for this great leap in progress.