Re: Message for Duane Hookum re Concatenate
From: Noel (anonymous_at_discussions.microsoft.com)
Date: 05/15/04
- Next message: Marshall Barton: "Re: Active Visual Basic Module"
- Previous message: Alex Dybenko: "Re: Active Visual Basic Module"
- In reply to: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Next in thread: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Reply: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 15 May 2004 09:48:58 -0700
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 “and”, 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’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
>>
>
>
>.
>
- Next message: Marshall Barton: "Re: Active Visual Basic Module"
- Previous message: Alex Dybenko: "Re: Active Visual Basic Module"
- In reply to: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Next in thread: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Reply: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
