concatenating names oddity

[THWatson]

Using Access 2000

The database has about 250 names, addresses, etc.

In a query that concatenates FirstName and LastName, all the names show up as follows...
Joe Smith
Adam Brown
Corey White etc. etc.

with the exception of one name that concatenates without a space between FirstName and LastName...
BrentMills

I have checked and rechecked the query and its underlying form, and the input form, and can find nothing in this record which would make it concatenate improperly.

Any clues?

Tom

 

[dhookom]

Tom,
You have participated in this forum long enough that you should know that we would like to see your sql view...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

Duane
A very appropriate comment. Yes, I certainly should know better. I didn't post the SQL because it seems so odd that this happens with only one name out of about 250. So I guess I was looking for something from way out in left field rather than something "normal."

Here's the SQL...

SELECT [Canadian Addresses].RecordID, [Canadian Addresses].Key, [Canadian Addresses].LastName1, [Canadian Addresses].FirstName1, IIf(IsNull([FirstName2]) And IsNull([LastName2]),[FirstName1],[FirstName1] & " and " & [FirstName2]) AS FirstNames, [Canadian Addresses].FirstName2, [Canadian Addresses].LastName2, IIf(IsNull([Children]),[FirstName1] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName1]),[LastName1] & " & " & [FirstName2] & " " & [LastName2]),[FirstName1] & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2])," ",", " & [FirstName2] & " ") & ("and "+[Children]) & " " & [LastName1]),[LastName1] & " & " & [FirstName2] & " " & [LastName2] & (" and "+[Children]))) AS FullNames, [Canadian Addresses].Address, [Canadian Addresses].SecondAddress, [Canadian Addresses].City, [Canadian Addresses].Province, [Canadian Addresses].Country, [Canadian Addresses].PostalCode, [Canadian Addresses].Prefix, [Canadian Addresses].HomePhone, [Canadian Addresses].OfficePhone, [Canadian Addresses].Fax, [Canadian Addresses].Greeting, [Canadian Addresses].CellPhone, [Canadian Addresses].Other, [Canadian Addresses].Comments
FROM [Canadian Addresses]
ORDER BY [Canadian Addresses].LastName1, [Canadian Addresses].FirstName1
WITH OWNERACCESS OPTION;

Tom

 

[THWatson]

Duane
As you can see from the SQL, this pulls together more than just FirstName and LastName. I was trying to simplify the matter in my original post.

But here are the results of a few records...
Barbara & Brent Miller
Lorinne, Jim and Andrea Martino
Erla Gervais
Valerie, Wayne and Katie & Lindsey Gullett
Kent and Quinten & Chloe Haddock
BrentMills & Judy McOstrich and Jackson & Zoe

Whatever follows after the "and" are Children, if there are any.

The one record that doesn't work is the 6th one in the results above. Obviously, it should be "Brent Mills" rather than "BrentMills."

Tom

 

[THWatson]

Duane
I found the problem. And, as you no doubt suspected, it lies in the SQL. A matter of a missing space. (reminds me of the book "Zen and the Art of Motorcycle Maintenance" where a missing shim renders the morotcycle useless)

The tip-off was that the problem only occurred in a situation where there were Children and where the two parents did not have the same last name. There was only one of these in the whole list of people, and that is what made me think there was an oddity somewhere.

I looked at the thing for a long time before I decided to post in the first place. I should have persisted. I would have found it eventually.

In any event, here is the corrected SQL...

SELECT [Canadian Addresses].RecordID, [Canadian Addresses].Key, [Canadian Addresses].LastName1, [Canadian Addresses].FirstName1, IIf(IsNull([FirstName2]) And IsNull([LastName2]),[FirstName1],[FirstName1] & " and " & [FirstName2]) AS FirstNames, [Canadian Addresses].FirstName2, [Canadian Addresses].LastName2, IIf(IsNull([Children]),[FirstName1] & " " & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2]),"","& " & [FirstName2] & " ") & [LastName1]),[LastName1] & " & " & [FirstName2] & " " & [LastName2]),[FirstName1] & IIf(IsNull([LastName2]),(IIf(IsNull([FirstName2])," ",", " & [FirstName2] & " ") & ("and "+[Children]) & " " & [LastName1])," " & [LastName1] & " & " & [FirstName2] & " " & [LastName2] & (" and "+[Children]))) AS FullNames, [Canadian Addresses].Address, [Canadian Addresses].SecondAddress, [Canadian Addresses].City, [Canadian Addresses].Province, [Canadian Addresses].Country, [Canadian Addresses].PostalCode, [Canadian Addresses].Prefix, [Canadian Addresses].HomePhone, [Canadian Addresses].OfficePhone, [Canadian Addresses].Fax, [Canadian Addresses].Greeting, [Canadian Addresses].CellPhone, [Canadian Addresses].Other, [Canadian Addresses].Comments
FROM [Canadian Addresses]
ORDER BY [Canadian Addresses].LastName1, [Canadian Addresses].FirstName1
WITH OWNERACCESS OPTION;

The missing a space was in the last bit of the concatenation of FullNames.
" " & [LastName1] & " & " & [FirstName2] & " " & [LastName2] & (" and "+[Children]))) AS FullNames

Thanks, Duane, for pushing me to post the SQL, as that also pushed me to keep looking for my own error...which in nested IIf expressions is sometimes difficult to spot.

By the way, how's your grandson?

Tom

 

[dhookom]

Jackson is great. He just turned two about a month ago. We are looking forward to helping everyone spoil him at Christmas. He is the first and only of his generation.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

Duane
We have 7 grandchildren.

Our oldest is in 2nd year University.

Our youngdest will be 1 year old this coming Sunday. We have been babysitting her 4 days a week for the past couple of months. What an awakening age! No "same old, same old" for her; every moment is a new experience.

Best regards.

Tom