Re: Message for Duane Hookum re Concatenate
From: Noel (anonymous_at_discussions.microsoft.com)
Date: 05/15/04
- Next message: Jan Vacek: "defaultvalue in textbox - MsAccess"
- Previous message: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- In reply to: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Next in thread: Noel: "Re: Message for Duane Hookum re Concatenate"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 15 May 2004 15:27:29 -0700
Thanks for taking the time to give so much detail Doug. I
see what you mean about the End If business - my mistake.
Ill have to leave it til tomorrow to try your other
suggestion re using the same Module code for all queries -
its late here in the UK now. Hopefully Ill understand it
well enough to get it all working. If not Ill get back to
you. Thanks again for all the help. Cheers, Noel
>-----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
>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: Jan Vacek: "defaultvalue in textbox - MsAccess"
- Previous message: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- In reply to: Douglas J. Steele: "Re: Message for Duane Hookum re Concatenate"
- Next in thread: Noel: "Re: Message for Duane Hookum re Concatenate"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
