-----Original Message-----
I did read your other note first, but decided to post here.
The only reason I can think of why it would have gone red is that you got
bit by the word-wrap problem.
strConcat = Left$(strConcat, Len(strConCat - Len (pstrDelim))
should all be on one line.
Rewritten such that I don't _think_ there'll be word-wrap:
If Len(strConcat) > 0 Then
strConcat = Left$(strConcat, _
Len(strConCat - Len(pstrDelim))
End If
The reason there's no longer an explicit -2 there is because now that you've
shown me Duane's code, I modified my suggestion to be consistent with his
code.
He's declared the function as
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ")
That means that pstrDelim is what the program is using between values. If
you don't supply a value to the function, it uses ", ". If you call it as X
= Concatenate(strSQL, " and "), then it'll put " and " between each value
(as I believe you said you were going to do). Since the function can
accomodate different delimiters, so too must the code to eliminate the last
delimiter!
As to some of the points you raise in your other post, no offence, but the
idea of having two virtually identical functions in your application isn't a
good one (at best, it can lead to confusion, and it can often mean that you
have to make code changes in multiple places). My suggestion would be to
modify the declaration of the function just a bit, to
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ", _
Optional pbooRemove As Boolean _
= True)
and change the code from
If Len(strConcat) > 0 Then
strConcat = Left$(strConcat, _
Len(strConCat - Len(pstrDelim))
End If
to
If pbooRemove = True Then
If Len(strConcat) > 0 Then
strConcat = Left$(strConcat, _
Len(strConCat - Len(pstrDelim))
End If
End If
This allows you to specify whether or not you want the last characters
removed. I've set it to default to removing the last delimiter: you can
easily change that by making that new parameter = False instead of = True.
You'd call this modified function as:
X = Concatenate(strSQL, " and ")
to use " and " as the delimiter, and to have the last " and " removed, or
X = Concatenate(strSQL, " and ", False)
if you want the last " and " left on the string.
As to you finding that you did not need the End, if you note, I put what to
do on a separate line than the If. When you do that, you must have the End.
Putting the condition and the statement on the same line is acceptable, but
my personal preference is to always put it on a separate line.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
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
(no e-mails, please!)
message
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
(no e-mails, please!)
message
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
.
.
.
