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.