Concatenate Clean (V2K SR1)

Topic

New Reply

I am creating a description field that concatenates several other fields and pads each field with a space. Since not all fields are populated, there were gaps showing up in the concatenated field. For instance, if the follwoing 4 fields were one string f1:Ford; f2:Fairmont; f3:””; f4:1.0 liter it would yield “Ford Fairmont 1.0 liter” with two spaces between Fairmont and 1.0 liter.

I used the following code to replace any series of double spaces with a single space:
>>>>>
Function StripExtraChars(PassedStr, RemoveExtraChar$)
On Local Error GoTo StripExtraChars_Err
Dim i As Integer, GotChar As Integer
Dim HoldStr As String, HoldChar As String

‘ Exit if passed value is Null.
If IsNull(PassedStr) Then Exit Function

‘ Trim extra characters from passed string.
PassedStr = Trim$(PassedStr)

‘ Cycle through string and remove extra
‘ string characters specified in the
‘ RemoveExtraChar value.
For i = 1 To Len(PassedStr)
HoldChar = Mid$(PassedStr, i, 1)
If HoldChar = RemoveExtraChar Then
If GotChar = False Then
GotChar = True
HoldStr = HoldStr & HoldChar
End If
Else
GotChar = False
End If

If Not GotChar Then
HoldStr = HoldStr & HoldChar
End If

Next i

StripExtraChars = HoldStr

StripExtraChars_End:
Exit Function

StripExtraChars_Err:
MsgBox Error$
Resume StripExtraChars_End
End Function
>>>>
It worked great, however, I was recently asked to pad the fields with a blank space AND a comma which would yield:
“Ford, Fairmont, , 1.0 liter”.
Simply changing the
HoldChar = Mid$(PassedStr, i, 1) to
HoldChar = Mid$(PassedStr, i, 2) doesn’t work. I tried several variations to clean the space and comma but have had no luck. Any help would be appreciated.

Reply | Quote

Replies

I would think that it would be better to test your fields before you concatenate them and ignore invalid fields rather than try to clean up after,
some thing like;

if not isNull([f1]) then strModel = [f1] & “, ”
if not isNull([f2]) then strModel = strModel & [f2] & “, ”
if not isNull([f3]) then strModel = strModel & [f3] & “, ”
if not isNull([f4]) then strModel = strModel & [f4] & “, ”
strModel = left(strModel,len(strModel)-2) ‘ to remove final comma and space

If you cannot test first for some reason you may be able to use the NZ() function to put in a known string that you can search for and then replace in your function.

something like [f1] & “, ” & NZ([f2], “XYZ”) & “, ” & NZ([f3], “XYZ”) & “, ” & NZ([f4], “XYZ”)

Then in your function you could use the replace function, Replace(strModel, “, XYZ”,””) which should take out all the spares.

This is all “air Code” as I don’t have Access at home and so will need some tweaking!

HTH

Peter

Reply | Quote

You can concatenate using “+” instead of “&”:

[f1] & (“, “+[f2]) & (“, “+[f3]) & (“, “+[f4])

The & operator handles Null values as if they are empty strings, but the + operator (when applied to strings) results in Null if one or both operands are Null:
“Ford” & Null = “Ford”
“Ford” + Null = Null

Reply | Quote

Isn’t it funny how something so simple can work so great. Hans, I was a bit skeptical but it worked perfectly. Thanks a bunch!!…and Peter thanks for the input as well.

Reply | Quote

yeah! but why use only 1 line of code when you can use dozens LOL

Peter

Reply | Quote

Hi Hans
Very handy to know that one, saves a lot of coding !!
Pat

Reply | Quote

It’s a great tip for working with Access but don’t try it in SQL Server. The “+” character *is* the concatenation operator there and behaves the way “&” does in Access/Jet.

Reply | Quote