string concatenation

[EVPLS]

Hi,

in Access 2003, I'm trying to "conditionally" concatenate strings. It's been
my understanding that the "+" symbol does the job. This is what I'm trying to
do:

If InStr(1, strInput, "A", vbTextCompare) > 0 Then _
varRegional = "Asia/Pacific"
If InStr(1, strInput, "E", vbTextCompare) > 0 Then _
varRegional = (varRegional + ", ") & "Europe"

strInput can be any 1 to 5 character combination of A, E, N, S and R (Asia,
Europe, NAFTA, South America and rest of world).

When processing the "Europe instruction", varRegional may be Null if "A"
were not in strInput. The "Europe" instruction always returns ", Europe",
including the trailing comma. I tried with and without the paratheses in the
"varRegional = ..." instruction.

Am I mistaken or missing out on something? References (in this order) are:
Visual Basic for Applications, Microsoft Access 11.0 Object Library,
Microsoft DAO 3.6 Object Library, Microsoft Visual Basic for Applications
Extensibility 5.3 and OLE Automation.

My company has just switched over from AC97 to AC2003. And they are known
for "crippling" applications. On my home computer, still with AC97 as
Microsoft designed it, this type of concatenation seems to work (playing with
sample data bases provided in John Viescas' book "Building Access 2003
Applications", he conjures up SQL strings that way). Needless to say, his
concatenation fails, too, on my office computer (SQL strings all have a
leading " AND " statement).

Any help is appreciated, thanks in advance.

 

[Allen Browne]

Ask Access what's in varRegional.

You may find that it is not a Null.
For example, it could be a zero-length string.

 

[Hendrik Müller]

Hi,

in Access 2003, I'm trying to "conditionally" concatenate strings. It's been
my understanding that the "+" symbol does the job. This is what I'm trying to
do:

what you try to do only works with NULL
but with:
Dim varRegional
it is only an EMPTY variant
=> EMPTY is not the same like NULL

missing initialization command is:

varRegional = Null

 

[Paolo]

Hi EVPLS,
if the first if is jumped 'cause no "asia/pacific", varRegional is void and
so you concatenate void with + ", ") & "Europe" and so the resulting string
is, as you said
",Europe".

To avoid this do as follow
If InStr(1, strInput, "A", vbTextCompare) > 0 Then _
varRegional = "Asia/Pacific"
If InStr(1, strInput, "E", vbTextCompare) > 0 Then _
if not isnull(varregional) then ' this to check if you already
wrote something in varregional
varRegional = (varRegional + ", ") & "Europe"
else
varRegional = "Europe"
endif
etc. etc.
It sounds me weird that it worked in Acc97
HTH Paolo

 

[EVPLS]

Hi,

thanks for one of the solutions.

@ Paolo: Maybe I had the variant filled with Null instead of it being empty
when working in AC97.

@ Allen Browne, Hendrik Müller: Thanks for pointing me to the "Null
mystery". Stupid me had forgotten to tell Access to write a Null value so
varRegional in fact was empty.

 

[Robert Morley]

In addition to what everyone else has been saying, try changing the + to an
& and see if that improves things at all. If varRegional is Null, as
suggested, the & will still produce the results you're expecting.

You may also want to consider changing varRegional to a String, assuming
it's okay for the final result to be an empty string instead of a Null.

As to the code itself, I would suggest something like the following - the
first example uses a String, second keeps it as a Variant.

Public Function StrTest(ByVal strInput As String) As String
Dim strRegional As String
Dim i As Long

For i = 1 To Len(strInput)
Select Case Mid$(strInput, i, 1)
Case "A"
strRegional = strRegional & ", Asia/Pacific"
Case "E"
strRegional = strRegional & ", Europe"
Case "N"
strRegional = strRegional & ", NAFTA"
Case "S"
strRegional = strRegional & ", South America"
Case "R"
strRegional = strRegional & ", Rest of World"
Case Else
MsgBox "Invalid input"
End Select
Next
StrTest = Mid$(strRegional, 3)
End Function

Public Function VarTest(ByVal strInput As String) As Variant
Dim varRegional As Variant
Dim i As Long

varRegional = Null
For i = 1 To Len(strInput)
Select Case Mid$(strInput, i, 1)
Case "A"
varRegional = varRegional & ", Asia/Pacific"
Case "E"
varRegional = varRegional & ", Europe"
Case "N"
varRegional = varRegional & ", NAFTA"
Case "S"
varRegional = varRegional & ", South America"
Case "R"
varRegional = varRegional & ", Rest of World"
Case Else
MsgBox "Invalid input"
End Select
Next
If Not IsNull(varRegional) Then varRegional = Mid$(varRegional, 3)
VarTest = varRegional
End Function

You could do it in a *much* shorter fashion than the above using Choose, but
I think it's less-readable and wouldn't handle errors. You can, of course,
elimination the "_"'s in the strRegional assignment; I've included them just
so that the line works properly with any news-reader-induced line-wrapping.

Public Function StrTest2(ByVal strInput As String) As String
Dim strRegional As String
Dim i As Long

For i = 1 To Len(strInput)
strRegional = strRegional & ", " & _
Choose(InStr("AENSR", Mid$(strInput, i, 1)), "Asia/Pacific", _
"Europe", "NAFTA", "South America", "Rest of World")
Next
StrTest2 = Mid$(strRegional, 3)
End Function


Rob