String concat with +?

[Maury Markowitz]

When you're building up a big string out of delimited optional
"stringlettes", the syntax can get pretty messy. For instance, lets
say we're building up a comma delimited list (because I am)...

If something Then
if len(myString)>0 Then myString = myString & ","
myString = myString & something
end if
If somethingelse Then
if len(myString)>0 Then myString = myString & ","
myString = myString & somethingelse
end if

I seem to recall there is a syntax to make a sort of "optional
contact" function using the "+" function instead of the "&". IIRC it
is something like...

myString = (myString + ",") & "350,400,1866"

But that's not it. Anyone know the trick?

Maury

 

[Arvin Meyer [MVP]]

You use the + operator to filter out NULLs and the & operator to include
them:

[FirstName] & (" " + [MI] + ".") & (" " + [LastName])

excludes the middle initial and period (.) and spaces and LastName if they
don't exist.

 

[Maury Markowitz]

So is my understanding that [anything] + [anything else] will always
result in no output if either one is null correct?

Maury

 

[Stefan Hoffmann]

hi Maury,

I seem to recall there is a syntax to make a sort of "optional
contact" function using the "+" function instead of the "&". IIRC it
is something like...

myString = (myString + ",") & "350,400,1866"

But that's not it. Anyone know the trick?

myString must be a variant, then this should work:

myString = myString & ("," + something)


mfG
--> stefan <--

 

[Danny Lesandrini]

Correct. This is a nice function if you want to force something to NULL in
a string concatonation.

SELECT ([LastName] + ', ') & [FirstName] & (' ' + [MiddleInitial])

In this example, the text bits, comma and spaces, are only added if/when the
fields are not NULL. Worst case scenario where only FirstName is populated,
the string will have no extra spaces or comma.

 

[Douglas J. Steele]

hi Maury,


myString must be a variant, then this should work:

myString = myString & ("," + something)

I don't see why myString needs to be a variant, since myString & Null will
return a string.

 

[Danny J. Lesandrini]

Doug, you're right.

In fact, that call will always return a string if myString is declared as a
string.

Danny

 

[Stefan Hoffmann]

hi Douglas,

I don't see why myString needs to be a variant, since myString & Null will
return a string.

Obviously I meant something not myString .)




mfG
--> stefan <--

 

[David W. Fenton]

This is a nice function if you want to force something to NULL in
a string concatonation.

SELECT ([LastName] + ', ') & [FirstName] & (' ' +
[MiddleInitial])

In this example, the text bits, comma and spaces, are only added
if/when the fields are not NULL. Worst case scenario where only
FirstName is populated, the string will have no extra spaces or
comma.

I would concatenate that this way:

Mid(("12" + LastName) & (", " + FirstName) & (" " + MiddleInitial),
3)

This fixes the situation where FirstName is Null and LastName is
not.

 

[Danny J. Lesandrini]

David:

That's clever and very nearly self documenting. At first, I so totally didn't
understand your code. I mean, it's nearly 10 pm here and I've been up since
4 am and I just had a couple of beers

The "12" in the beginning tricked me, but now I see the genious of it.

 

[David W. Fenton]

That's clever and very nearly self documenting. At first, I so
totally didn't understand your code. I mean, it's nearly 10 pm
here and I've been up since 4 am and I just had a couple of beers

The "12" in the beginning tricked me, but now I see the genious of
it.

I believe it was Trevor Best who clued me into this method.

I use it in code all the time, too, when concatenating a string. I
used to write something like this:

If Len(strMyString) = 0
strMyString = strOtherString
Else
strMyString = ", " & strOtherString
End If

Now, I just do:

strMyString = ", " & strOtherString

and chop off the first two characters of the end result with Mid().

 

[Arvin Meyer [MVP]]

That's exactly correct.

 

[Maury Markowitz]

myString = myString & ("," + something)

Ok, so if this syntax is correct, can anyone offer a reason why it's
not working for me? Is it the data type of myString?

Maury

 

[Douglas J. Steele]

What does "not working" mean? Are you getting an error? If so, what's the
error? If you're not getting an error, what are you getting, and what did
you expect to get instead?

What's your complete code (including declarations). Trying to solve a coding
problem with only a single line of code is somewhat akin to trying to solve
an electrical problem having only been shown a single light bulb.

 

[David W. Fenton]

:

Ok, so if this syntax is correct, can anyone offer a reason why
it's not working for me? Is it the data type of myString?

If myString is a string, it won't work, because string variables
can't store Nulls, but only zero-length strings. So, there is no
Null propagation with a string data type. If you want to propagate
Nulls stored in a variable, you have to use Variant data type for
myString.

 

[Douglas J. Steele]

:


If myString is a string, it won't work, because string variables
can't store Nulls, but only zero-length strings. So, there is no
Null propagation with a string data type. If you want to propagate
Nulls stored in a variable, you have to use Variant data type for
myString.

Don't you mean something needs to be a variant, not myString?

Since you're using & to concatenate to myString, it's fine as a string.

 

[Stefan Hoffmann]

hi Douglas,

Douglas J. Steele wrote:
So, there is no

Don't you mean something needs to be a variant, not myString?

I'm not sure about "propagate Nulls"... using an earlier example:

Mid(("12" + LastName) & (", " + FirstName) & (" " + MiddleInitial), 3)

becomes

Mid(("12" + LastName) + ((", " + FirstName) & (" " + MiddleInitial), 3))

when you only want a value, if LastName is not null.

In the second case you need a variant to store the result.


mfG
--> stefan <--

 

[David W. Fenton]

Don't you mean something needs to be a variant, not myString?

Since you're using & to concatenate to myString, it's fine as a
string.

You're correct, of course.

 

[David W. Fenton]

Douglas J. Steele wrote:
So, there is no
I'm not sure about "propagate Nulls"... using an earlier example:

Mid(("12" + LastName) & (", " + FirstName) & (" " +
MiddleInitial), 3)

becomes

Mid(("12" + LastName) + ((", " + FirstName) & (" " +
MiddleInitial), 3))

when you only want a value, if LastName is not null.

In the second case you need a variant to store the result.

The original expression was intended for use in a query, but others
introduced variables.

I, for one, never store the middle initial/middle name in a separate
field -- I always just make it part of the firstname. This obviates
any need for concatenation.

I went through all the trouble of breaking down name fields into
Honorific, Firstname, Middle, Lastname and Suffix back in my Paradox
days, and eventually discovered that the value of doing all of this
was incredibly slight. In most of my apps these days, there's
nothing but Firstname and Lastname, and it seems to do the job just
fine.

For specialized applications, such as a mailing list program where
such things can start to matter (e.g., you need to say "Dear Mr.
Fenton" in the saluation of a letter), I would do things
differently. But for non-specialized apps, I don't muck around with
the mess of breaking down the data into its component parts,
precisely because those component parts are never used individually
in any circumstance.