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?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Catherine

    Concatenation of blank and text fields

    Catherine, Jul 25, 2003, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    654
    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:
    300
    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:
    580
    Duane Hookom
    Nov 14, 2003
  4. Ray

    Concatenation

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

    Wild Card Concatenation With User Input

    Roy, Feb 27, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    332
    [MVP] S.Clark
    Mar 1, 2004
  6. Guest

    Concatenation

    Guest, Mar 11, 2004, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    287
    John Vinson
    Mar 11, 2004
  7. Don Hicks

    Concatenation Dilemma

    Don Hicks, Mar 27, 2004, in forum: Microsoft Access Queries
    Replies:
    8
    Views:
    238
    Don Hicks
    Mar 29, 2004
  8. Guest

    Query concatenation help

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