Combines the different fields of an address into a single field -- One Error????

[TenOc]

I have a query that combines the different fields of an address into a single field. Everything works well except for one address and I cannot fear out why this does not combine. Any suggestions on what I've should look for?

[orange]

I have a query

Please show readers the query sql.

[davegri]

Maybe it's that pound sign #

Try this to get rid of it:

Combined: Replace(Addr1,"#","") & " " & Addr2

[Welshgasman]

I am going to hazard a guess due to the # in the address?, but without seeing the code, that is all it is?, just a guess, based on the rest of the pic.
Though from both the pics, I am not even sure that is the problem, as what is in the first pic seems to be what is in the second pic.?

[TenOc]

I am going to hazard a guess due to the # in the address?, but without seeing the code, that is all it is?, just a guess, based on the rest of the pic.
Though from both the pics, I am not even sure that is the problem, as what is in the first pic seems to be what is in the second pic.?

I did remove the # --still No City State and Zip. There is no code, simply a query.

Address: [tabBenef]![Address1] & ", " & [tabBenef]![Address2] & ", " & [tabBenef]![City] & ", " & [tabBenef]![State] & ", " & [tabBenef]![ZIPCode]

[Micron]

How is the indicated record (4) any different from 2,5,6,8,9,10? They're all missing names as well? Or is this about the lack of city, state and zip in record 4?
Best not to just show pics and expect everyone to figure out what the problem is. Some people are very visual oriented, some not so much. You definitely have the advantage when you know what the issue is but don't point it out.

I think data would tell more than the query sql. Could have something to do with Address 2 records, such as empty strings vs nulls as some records seem to have , , and some don't. I suspect , , is a concatenation of missing Address 2 in the middle, so it ought to be consistent. Is this based on code that concatenates fields over each record?

EDIT - last post wasn't there for me when I wrote that. Suspect data more so now.

[TenOc]

Since some of the data is "confidential" I only took a screen shot of the address and not the name and other data. The only problem is with this one record and the city, state, zip. The concatenations works well with blank (null) data in address 2. I did delete the complete address and then re-entered the address. I also repaired and compacted the data base. still no joy.

When I view in table view all looks well

A view of the Query with the city state zip listed as well as the combined address Same problem

[Micron]

You could remove all that isn't necessary to replicate the issue and use this Randomizing Data for Posted db's
on the sensitive data. I suspect it will be the fastest way to get a solution or at least ID the problem. If your db is split you'd need to post the be as well, but do not muck up your linked tables because they'll be linked to the original db you have. You'd have to put both be and fe in a different folder, relink those and then delete/randomize stuff.

I have tried to figure it out based on your pic(s) but am clueless since we seem to have ruled out # character, plus there's only 1 table involved so it can't be about joins. You could try this too:

http://allenbrowne.com/func-concat.html

EDIT - lightbulb moment.
Drag the row height to double and make sure it's not on another line. If it is, wherever you get the data from has line wrap characters - at least on that record. You could even have left it there when re-entering, or simply have put it back somehow.

[TenOc]

Y
EDIT - lightbulb moment.
Drag the column height to double and make sure it's not on another line. If it is, wherever you get the data from has line wrap characters - at least on that record. You could even have left it there when re-entering, or simply have put it back somehow.

SOVLED!!! There was some "hidden" data. Once I double the height I was able to see the other data and remove it.

[Micron]

Great! I edited my post from 'column' to row because I meant row height.
Please use "Thread Tools" to mark this as solved if you think it's a done deal.