Strange behavior in query window

[Seth Schrock]

In a query, I'm trying to concatenate customer names together into a single field.

([CustFN] & (' '+[CustMI]) & (' '+[CustLN])) AS CustName

When I run this query, only the first name displays. However, if I click inside the field, then the middle initial and last name show up, but just in the one cell. When I click out of it, then that name goes back to just being the first name. I have never seen this before.

[NeoPa]

Is the column wide enough to display the data?

[Seth Schrock]

More than enough for the combination of all three fields.

[GKJR]

Is this in a saved query or is it an SQL statement used as source data for an object?

[Seth Schrock]

This is in a querydef.

[NeoPa]

Sorry Seth. In that case I've no idea what may be going on.

[GKJR]

I don't get it either. It seems like the other values are being stored in different columns, but that wouldn't make sense for a querydef. The only time I've seen anything like this is in a control with multiple columns. What happens if you take out the custMI as a test and just concatenate the first and last name without (+)?

[NeoPa]

Also, check what the actual value of the field is. It may appear the way you say if there are CR or LF characters in the value anywhere. Maybe they are in your code and maybe they are embedded within the field values of the records themselves.

It would explain why you're seeing what you're seeing.

[GKJR]

What are CR and LF character?

[Seth Schrock]

CR is Carriage Return and LF is Line Feed, Chr(10) and Chr(13). I'm not sure which one is Chr(10) and Chr(13) as they are almost always together.

Removing the CustMI doesn't change anything, nor does including each field individually. I tried using the Replace() function to remove the Chr(10) and Chr(13) characters, but that didn't change anything. I also tried changing my concatenation characters to all ampersands, but again, there was no change.

[zmbd]

chr(10) = LF
chr(13) = CR
-
If even one return is longer than your field width then you'll find this happens, and it's a pain to T.S. without the length

What I did in my form was return the length of the string
such as:

LEN([CustFN] & (' '+[CustMI]) & (' '+[CustLN])) AS CustName 
 

then in the conditional, ">=" field width.
For me, this returned only one record that was 2 charactors too long and that was the issue.

[Seth Schrock]

Well, every field that has data in it returns a Len() of 16 even if it is only displaying 5 characters. I tried using the Trim() function to take away spaces, but the padding character must not be a space as the Trim() function didn't do anything.

[GKJR]

Are the text fields in the table set to that length? I always interpreted that setting as the maximum length but is there some way that it automatically designates the length as 16 even if it doesn't use that many characters?

[GKJR]

Also, if you change the column width in your query to be much wider, does it show the rest of the name with a bunch of spaces in between?

[Seth Schrock]

I'm using a SQL Server backend that has column level encryption on these fields, which I believe is what is causing the padding. And no, making the query column very wide doesn't show the fields I'm needing.

[GKJR]

I'm afraid I'm not going to be much help on that if you're using SQL Server, sorry. I just noticed that you have a semi-colon after your Ampersand in the original post. Was that intentional?

[GKJR]

Sorry forget that. I viewed it wrapped and it put a semi-colon there. When I unwrapped it it was still there...

[Seth Schrock]

Neither ampersand has a semi-colon after it in the OP. I would be getting an error message if there was.

[Rabbit]

What's the ASCII value of the padding characters?

[Seth Schrock]

I don't know how to find that out.

[Rabbit]

Asc(Right([Field Name], 1))
ASC() if you're using Access, ASCII() if you're using a pass through.

[Seth Schrock]

It returns a 0. I tried doing a replace on that character, but that didn't change anything on the length.

[Rabbit]

Can you post the full SQL you are now using?

[Seth Schrock]

SELECT tblCustomers.CustID_pk
, tblCustomers.LoanID_fk
, ([CustFN] & (' '+[CustMI]) & (' '+[CustLN])) AS CustName
, Asc(Right([CustFN], 1)) As Character
, Len(Replace(CustFN, Asc(Right([CustFN], 1)), '')) AS Length
FROM tblCustomers;
 

[Rabbit]

You're replacing the ascii result of 0. The ASC function was just to find the character. You need to replace the actual character. Use CHR(0) instead.

[Seth Schrock]

Well, I'm now on the right track as now most of the names are being returned. I believe that I just need to handle the nulls and then I'll be good to go. Thanks Rabbit.

[Rabbit]

No problem, good luck on the rest of your project.

[NeoPa]

Can you confirm that the data was all there as expected but, because it was padded with Chr(0)s it wasn't showing?

[Seth Schrock]

Unfortunately, I can't confirm that because I was never able to stretch out the cell and see the other data. However, what I ended up with would seem to confirm that because all I ended up doing was a single replace statement on the concatenated data. So the computer seems to say that that data was all there as expected, but I wasn't able to visually verify that.

[NeoPa]

Cheers. If the following snippet in your SQL caused the data to show then that's confirmation enough for me :

Replace([CustFN] & (' '+[CustMI]) & (' '+[CustLN]),Chr(0),'')  AS [CustName]

Although the following may be a better solution assuming [CustLN] is guaranteed to be populated whereas the other two are not (which is the usual way of name data) :

Replace(([CustFN]+' ') & ([CustMI]+' ') & [CustLN],Chr(0),'')  AS [CustName]