Adding strings and incorporating correct punctuation.

[Guest]

I have a form in which users may select any number of six phrases. I want to
combine the phrases, which is easy (Phrase1 + phrase2 + etc..). However, I'd
like to be able to incorporate comma marks and a period between the selected
phrases. The way I have it now, if the user only selects phrase3 for
example, I get two ill-placed punctuation (ie. "You have selected" + Phrase1
+ ", " + Phrase2 + ", " + Phrase3 + etc. turns into "You have selected, ,
Phrase3, , , ,.)

Is there a way to corretly add the selected strings up?
I currently have been working on very complicated If..then statements such as:
If Phrase1 = "" then
Phrase1 = ""
If Phrase2 = "" then
Phrase2 = ""
If Phrase3 = "" then
Phrase3 = ""
Else Phrase3 = "String expression"
.... and this goes on and on trying to test every possible combination.

Any suggestions?

Thansk!

 

[Allen Browne]

If the blank ones are Nulls (not zero-length strings), you can take
advantage of a subtle difference between the two concatenation operators in
VBA:
"A" + Null => Null
"A" & Null => "A"

strResult = "You have selcted " & Phrase1 + "," & Phrase2 + "," & ...

The alternative is a bunch of If ... Then ... constructs:
Dim strResult As String
Dim lngLen As Long
Const strcSep = ", "
If Nz(Phrase1, vbNullString) <> vbNullString Then
strResult = strResult & Phrase1 & strcSep
End If
If Nz(Phrase2, vbNullString) <> vbNullString Then
strResult = strResult & Phrase2 & strcSep
End If
...
lngLen = Len(strResult) - Len(strcSep) 'without trailing comma.
If lngLen <= 0 Then
strResult = "Say what?"
Else
strResult = "You have selected " & Left$(strResult, lngLen)
End if

It might be easier to loop through an array than handle individual strings.

Or, if you like the first approach better but have to handle zero-length
strings, you could convert them to nulls:
Function Str2Null(varIn As Variant) As Variant
'Purpose: Convert the input to Null if it's a zero-length string.
If varIn = vbNullString Then
Str2Null = Null
Else
Str2Null = varIn
End If
End Function

 

[Guest]

Amazing! Never knew the subtle difference (or, if I did I never understood it
until now) but it is helpful. I went with that approach instead of the
If..EndIf. Really works great. Then, with your little reminder of the Len
function, I got rid of my trailing comma and added a period. Wonderful!
Thanks again!!