Need help with concatenation function

Discussion in 'Microsoft Access Queries' started by EllenM, May 21, 2010.

  1. EllenM

    EllenM Guest

    Hello,
    I've found a wonderful concatenation function at
    http://www.mvps.org/access/modules/mdl0008.htm. It, however, separates the
    concatenated values with semicolons when I'd like them to be separated with
    "<br />"s.

    For instance, instead of "Value1; Value2; Value3", I'd like "Value1<br />
    Value2<br /> Value3<br />".

    Thanks in advance for your help.

    Ellen
     
    EllenM, May 21, 2010
    #1
    1. Advertisements

  2. EllenM

    Bob Barrows Guest

    EllenM wrote:
    > Hello,
    > I've found a wonderful concatenation function at
    > http://www.mvps.org/access/modules/mdl0008.htm. It, however,
    > separates the concatenated values with semicolons when I'd like them
    > to be separated with "<br />"s.
    >
    > For instance, instead of "Value1; Value2; Value3", I'd like
    > "Value1<br /> Value2<br /> Value3<br />".
    >
    > Thanks in advance for your help.
    >
    > Ellen


    I don't have the function in front of me, but surely it can't be too hard to
    change the '& ";" 'bit to '& "<br />"'

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, May 21, 2010
    #2
    1. Advertisements

  3. On Fri, 21 May 2010 10:57:01 -0700, EllenM <>
    wrote:

    >Hello,
    >I've found a wonderful concatenation function at
    >http://www.mvps.org/access/modules/mdl0008.htm. It, however, separates the
    >concatenated values with semicolons when I'd like them to be separated with
    >"<br />"s.
    >
    >For instance, instead of "Value1; Value2; Value3", I'd like "Value1<br />
    >Value2<br /> Value3<br />".
    >
    >Thanks in advance for your help.
    >
    >Ellen


    Just fix the code to your needs: where it has

    With lors
    If .RecordCount <> 0 Then
    'start concatenating records
    Do While Not .EOF
    lovConcat = lovConcat & lors(stFldToConcat) & "; "
    .MoveNext
    Loop
    Else
    GoTo Exit_fConcatFld
    End If
    End With

    change it to

    With lors
    If .RecordCount <> 0 Then
    'start concatenating records
    Do While Not .EOF
    lovConcat = lovConcat & lors(stFldToConcat) & " <br /> "
    .MoveNext
    Loop
    Else
    GoTo Exit_fConcatFld
    End If
    End With
    --

    John W. Vinson [MVP]
     
    John W. Vinson, May 21, 2010
    #3
  4. EllenM

    John Spencer Guest

    you would also need to fix the code in one other spot near the bottom of the
    routine. If you want "<br /> " at the end of the string or not.

    Either
    fConcatFld = lovConcat
    OR
    fConcatFld = Left(lovConcat, Len(lovConcat) - Len("<br /> "))
    instead of
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)




    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County

    John W. Vinson wrote:
    > On Fri, 21 May 2010 10:57:01 -0700, EllenM <>
    > wrote:
    >
    >> Hello,
    >> I've found a wonderful concatenation function at
    >> http://www.mvps.org/access/modules/mdl0008.htm. It, however, separates the
    >> concatenated values with semicolons when I'd like them to be separated with
    >> "<br />"s.
    >>
    >> For instance, instead of "Value1; Value2; Value3", I'd like "Value1<br />
    >> Value2<br /> Value3<br />".
    >>
    >> Thanks in advance for your help.
    >>
    >> Ellen

    >
    > Just fix the code to your needs: where it has
    >
    > With lors
    > If .RecordCount <> 0 Then
    > 'start concatenating records
    > Do While Not .EOF
    > lovConcat = lovConcat & lors(stFldToConcat) & "; "
    > .MoveNext
    > Loop
    > Else
    > GoTo Exit_fConcatFld
    > End If
    > End With
    >
    > change it to
    >
    > With lors
    > If .RecordCount <> 0 Then
    > 'start concatenating records
    > Do While Not .EOF
    > lovConcat = lovConcat & lors(stFldToConcat) & " <br /> "
    > .MoveNext
    > Loop
    > Else
    > GoTo Exit_fConcatFld
    > End If
    > End With
     
    John Spencer, May 21, 2010
    #4
  5. EllenM

    EllenM Guest

    Thanks, John

    And change:
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

    to
    fConcatFld = Left(lovConcat, Len(lovConcat) - 8)

    Now it's a masterpiece.
     
    EllenM, May 21, 2010
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Catherine

    Concatenation of blank and text fields

    Catherine, Jul 25, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    554
    John Spencer (MVP)
    Jul 26, 2003
  2. Sidney Linkers

    Re: aggregate concatenation

    Sidney Linkers, Oct 17, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    223
    Sidney Linkers
    Oct 18, 2003
  3. John Smith

    Concatenation of detail-records in a Master-Detail relationship

    John Smith, Nov 13, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    434
    Duane Hookom
    Nov 14, 2003
  4. Ray

    Concatenation

    Ray, Dec 17, 2003, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    203
    Steve Schapel
    Dec 17, 2003
  5. Guest

    Query concatenation help

    Guest, Oct 4, 2007, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    125
    Guest
    Oct 5, 2007
Loading...

Share This Page