Re: Message for Duane Hookum re Concatenate

From: Noel (anonymous_at_discussions.microsoft.com)
Date: 05/15/04 

Date: Sat, 15 May 2004 12:50:19 -0700

Hi Doug. Could you have another look at that? If I put
exactly what you say, where you say to, the code
immediately goes red and when I try to run the query I get
a Syntax error message. Also, what happened to the -2 bit
at the end? Or does your revised code take care of that
somehow? Thanks again, Noel
>-----Original Message-----
>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
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>

Loading