>>Hi,
>>
>>I am troubleshooting the following issue. And I can't figure out what causes it.
>>
>>A somewhat long SQL Select has the following segment:
>>

>>REPLICATE(' ',10-LEN(ALLTRIM(REFERENCE)))
>>

>>
>>The purpose of the above is to create a string of entity codes (nbsp) equal to the number of spaces after some entry in a field. The field name is REFERENCE. It used to be up to 10 characters in length. And the user was able to enter only up to 10 characters. And the above code worked.
>>Then I increased the length of the field to 15. But the entry in the field is still the same, less or equal to 10 characters.
>>Now the above code does not work. The SQL Select returns the error:
>>

>>String is too long to fit.
>>

>>
>>If I change the 10 in the above REPLICATE with 15, no error.
>>

>>REPLICATE(' ',15-LEN(ALLTRIM(REFERENCE)))
>>

>>
>>I don't understand why? Since the entry in the REFERENCE field is still only up 10 characters, the code 10-LEN... should work. Unless I miss something obvious.
>
>Definitely strange. Some ideas:
>
>1. "Reference" sounds like a VFP reserved word, can you test with some other name for that field?
>2. Can you apply an alias for it i.e. LEN( ALLTRIM( SomeAlias.Reference ) )
>3. Could you use something like PADR( ) to simplify your code or work around this issue?
>
>Another thing to be wary of is if you're specifying a variable-length output column in a VFP SELECT, the length of that column in the final output will be determined by the length of the result of that expression the first time it gets evaluated. This is a guess, but if the first evaluated length is shorter than the result of later evaluations, then maybe VFP is trying to cram 10 pounds of characters into a 5 pound sack so it throws the error you're seeing.
>
>The only safe way I know to avoid this kind of issue is to avoid variable-length expressions in output columns i.e. always CAST( ), PADR( ) them (or similar) to some fixed length.

I didn't try with another field named differently than Reference.
I can try an alias.
I think I did try PADR() to make sure that the first record does not create a short column. To no avail.
It is strange though that the number in the expression has to be greater or equal than the field length. And I did test by changing the length of the field back to 10. And then the original expression with 10 works.
I even tested the value that the expression 10-LEN(ALLTRIM(REFERENCE) calculates. The numbers are all pretty much within 0 to 10. And I tested with SUBSTR("Entry",0) and it works.
And yet, when the field is 15 characters I get the error, as I wrote above.
Thank you for your input and suggestions.