Re: Message for Duane Hookum re Concatenate
From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 05/15/04
- Next message: Noel: "Re: Message for Duane Hookum re Concatenate"
- Previous message: Tim Ferguson: "RE: Too few parameters"
- In reply to: Noel: "Re: Message for Duane Hookum re Concatenate"
- Next in thread: Noel: "Re: Message for Duane Hookum re Concatenate"
- Reply: Noel: "Re: Message for Duane Hookum re Concatenate"
- Reply: Noel: "Re: Message for Duane Hookum re Concatenate"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 15 May 2004 17:36:11 -0400
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 http://I.Am/DougSteele (no e-mails, please!) "Noel" <anonymous@discussions.microsoft.com> wrote in message news:d8c601c43ab5$da28b910$a501280a@phx.gbl... > 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 “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: Noel: "Re: Message for Duane Hookum re Concatenate"
- Previous message: Tim Ferguson: "RE: Too few parameters"
- In reply to: Noel: "Re: Message for Duane Hookum re Concatenate"
- Next in thread: Noel: "Re: Message for Duane Hookum re Concatenate"
- Reply: Noel: "Re: Message for Duane Hookum re Concatenate"
- Reply: Noel: "Re: Message for Duane Hookum re Concatenate"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
