Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenate Clean (V2K SR1)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Clean (V2K SR1)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Concatenate Clean (V2K SR1)

    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

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Clean (V2K SR1)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenate Clean (V2K SR1)

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

    Peter

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Concatenate Clean (V2K SR1)

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

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenate Clean (V2K SR1)

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •