Concatenating long strings doesn't work - why?

[Guest]

This works fine...

myString = "Hello"
myString = myString & " world"

However, if either of those two lines is longish, the string is cut off.
Further concats do nothing.

Can someone explain why an objects that supposedly holds 2^31 chars can't be
extended past about 80? And what do I do to fix it?

Maury

 

[Tom Ellison]

Dear Maury:

What is the context of your situation? Is this occurring in VBA code, when
viewing the value in the immediate pane? Are you seeing this in a control
on a form? Are you looking in a datasheet?

I think we'll need to know both the source of this data and the place where
you're seeing this.

Tom Ellison

 

[Guest]

What is the context of your situation? Is this occurring in VBA code, when

viewing the value in the immediate pane? Are you seeing this in a control
on a form? Are you looking in a datasheet?

I am making a very long SELECT statement that I then use as the
recordsource of a form. There is some semi-clever logic to ensure that the
various filtering selections in the form are applied correctly, which has
effects on the WHERE. Thus the string has to be built up part-by-part in the
code. The resulting string is about 1k long.

The string really is clipped. The recordsource coughs an error and returns
a clipped string as the sql.

Maury

 

[Albert D.Kallal]

Hum, perhaps using some function(S) on the string that are limited to 255
characters....

For example, if you try and return a memo field from sql, but surround the
memo field with a function, it often gets truncated to 255 chars.

However, your case sound different....

I would suggest using the debugger...step thought the code in question....

most common mistake in code that concatenation strings is

strSql = "select * from tableCustomer " & _
"where "

strSql = "City = 'Edmonton'"

In the above...the 2nd line is wrong...and should read...

strSql = sqlSql & "City = 'Edmotnon'"

Also, be aware that when you use "+" to concat values, null WILL prorogate,
and if you use "&", then nulll will not....

This quirk of VBA can be used to your advantage.....

eg:

UserNAme = "(FirstName) & (" " + MiddleInitial) & (" " + LastName))

In the above, if you don't have a middle name, then the extra space would
NOT be inserted...
And, the same goes for the last name....no extra space on the end if no
middle, or last name. However, with either a middle name, or a last
name...it works. This simply expression eliminates some quite complex iff()
statements to achieve the same result...
However, if you use

UserNAme = "(FirstName) & (" " & MiddleInitial) & (" " & LastName))

In the above...you will ALWAYS have a extra space when no middle name (or
last name) is present. Using & means nulls don't propagate through the
expression.

So, check your syntax. And check for "appropriate" use of "+" or "&" to
concat strings....

note that

"hello" + null value = null
"hello" & null value = "hello"

 

[Guest]

Hum, perhaps using some function(S) on the string that are limited to 255
characters....

Ok, this is something I will look for.

UserNAme = "(FirstName) & (" " + MiddleInitial) & (" " + LastName))

Oh, I will use this!

Maury