Re: Message for Duane Hookum re Concatenate

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 05/15/04 

Date: Sat, 15 May 2004 14:19:19 -0400

pstrDelim contains the delimiter you're putting between values (it defaults
to ", ", but you were talking about using " and " instead).

strConcat is what you want to alter.

Immediately before the statement

   Concatenate = strConcat

Put

   If Len(strConcat) > 0 Then
      strConcat = Left$(strConcat, Len(strConCat - Len(pstrDelim))
   End If 

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Noel" <anonymous@discussions.microsoft.com> wrote in message
news:d98901c43a9c$84ac73a0$a301280a@phx.gbl...
> Hi again Doug. Sorry to be dim but I cant get this to
> work. First, am I correct in assuming that  I should
> change your code by exchanging strMyString with something
> in Duanes code? Secondly, where should I add your code?
> Ive tried using pstrDelim as follows but when I put this
> at the top of  Duanes code I get the message End If
> without Block If. This seems strange because the If and
> End If words turn blue, indicating that they are
> recognised. As you can probably tell, im not too good at
> code building.
>
> If Len(pstrDelim) > 0 Then pstrDelim = Left$(pstrDelim, Len
> (pstrDelim) - 2)
> End If
>
> I have copied Duanes full code below in the hope that you
> could help me out a bit more. What exactly would your code
> be and where exactly should I add it?
>
> Thanks again for your help. Cheers, Noel
>
>
>
>
> Option Compare Database
> Option Explicit
>
> 'begin code
> Function Concatenate(pstrSQL As String, _
>                       Optional pstrDelim As String = ", ")
> _
>                               As String
>
>             'example 'tblFamily with FamID as numeric
> primary key
>             'tblFamMem with FamID, FirstName, DOB,...
>             'return a comma separated list of FirstNames
>             'for a FamID
>             '             John, Mary, Susan
>             'in a Query
>             'SELECT FamID,
>             'Concatenate("SELECT FirstName FROM tblFamMem
>             ' WHERE FamID =" & [FamID]) as FirstNames
>             'FROM tblFamily
>             '
>
>             '======For DAO uncomment next 4 lines=======
>             '====== comment out ADO below =======
>             Dim db As DAO.Database
>             Dim rs As DAO.Recordset
>             Set db = CurrentDb
>             Set rs = db.OpenRecordset(pstrSQL)
>
>             '======For ADO uncomment next two lines=====
>             '====== comment out DAO above ======
>            'Dim rs As New ADODB.Recordset
>            'rs.Open pstrSQL, CurrentProject.Connection, _
>                         adOpenKeyset, adLockOptimistic
>            Dim strConcat As String
>            'build return string
>            With rs
>                      If Not .EOF Then
>                           .MoveFirst
>                           Do While Not .EOF
>                                    strConcat = strConcat &
> _
>                                         .Fields(0) &
> pstrDelim
>                                   .MoveNext
>                           Loop
>                 End If
>                     .Close
>            End With
> Set rs = Nothing
>            '====== uncomment next line for DAO ========
>            'Set db = Nothing
>            Concatenate = strConcat
> End Function
> 'End Code
>
> >-----Original Message-----
> >Assuming your code is always adding ", " at the end of
> each field, all you
> >need to do is check to ensure that the string has
> content, and, assuming it
> >does, remove the last 2 characters:
> >
> >   If Len(strMyString) > 0 Then
> >      strMyString = Left$(strMyString, Len(MyString) - 2)
> >   End If
> >
> >If you wanted, you could even get really clever and use
> InStrRev (assuming
> >Access 2000 or newer) to determine where the last comma
> is, and change it to
> >"and" or "or"
> >
> >And while your concern about reproducing Duane's code is
> admirable, I'm
> >assuming he gave you the code through the newsgroup, in
> which case it's in
> >the public domain (it'll be archived at Google for
> everyone to see). All of
> >the MVPs that I know have no concerns about code we've
> posted to the
> >newsgroups.
> >
> >-- 
> >Doug Steele, Microsoft Access MVP
> >http://I.Am/DougSteele
> >(no e-mails, please!)
> >
> >
> >"Noel" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:d88e01c43a69$a79cba60$a301280a@phx.gbl...
> >> Hi Duane. Some time ago you gave me a copy of your
> >> Concatenate code which Ive used successfully on a number
> >> of occasions. In those cases I passed carriage return
> and
> >> line feed characters to your code which made the
> >> concatenated fields appear in columns.  I now want to
> >> create a Mail Merge letter and I wonder if its possible
> to
> >> modify the way the way the concatenated fields would
> >> appear in a sentence. Your code automatically injects a
> >> comma after each concatenated value, so a typical
> sentence
> >> using  concatenated first names might read
> >>
> >> The Smith family consists of Sally, Tom, Mary,.
> >>
> >> Is there a way I could change things so that the comma ,
> >> or the word &#8220;and&#8221;, appears between the concatenated
> fields
> >> but not after the last one? Then I could have
> >>
> >> The Smith family consists of  Sally, Tom, Mary.   Or
> >>
> >> The Smith family consists of  Sally and  Tom and  Mary.
> >> The full stop would be added in Word.
> >>
> >> For others reading this, I think the relevant part of
> the
> >> code is as follows. I don&#8217;t know if its OK to give the
> >> full code here without Duanes permission.
> >>
> >> Function Concatenate(pstrSQL As String, _
> >>                       Optional pstrDelim As String
> = ", ")
> >> _
> >>                               As String
> >>             'example 'tblFamily with FamID as numeric
> >> primary key
> >>             'tblFamMem with FamID, FirstName, DOB,...
> >>             'return a comma separated list of FirstNames
> >>             'for a FamID
> >>             '             John, Mary, Susan
> >>             'in a Query
> >>             'SELECT FamID,
> >>             'Concatenate("SELECT FirstName FROM
> tblFamMem
> >>             ' WHERE FamID =" & [FamID]) as FirstNames
> >>             'FROM tblFamily
> >>             '
> >> Thanks, Noel
> >>
> >
> >
> >.
> >

Relevant Pages

  • Re: Message for Duane Hookum re Concatenate
    ... Function Concatenate(pstrSQL As String, _ ... >>Doug Steele, Microsoft Access MVP ... >>> in Duanes code? ... >>>>> Concatenate code which Ive used successfully on a ...
    (microsoft.public.access.modulesdaovba)
  • Re: Concatenate from a Combo Box
    ... Doug Steele, Microsoft Access MVP ... Function fConcatChild(strChildTable As String, _ ... ' Quantity = Field name to concatenate ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.forms)
  • Re: Message for Duane Hookum re Concatenate
    ... at the top of Duanes code I get the message End If ... >> Concatenate code which Ive used successfully on a number ... >> concatenated fields appear in columns. ... >> The Smith family consists of Sally, Tom, Mary,. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Concatenate FirstName and LastName field and include a space
    ... Doug Steele, Microsoft Access MVP ... (no private e-mails, please) ... I want to concatenate the two ...
    (microsoft.public.access.queries)