Concatenate multiple rows into one string

Hi,

I have the following table:

Project Name email_addr
happy jane.smith@happy.com
happy john.smith@lappy.com
happy mary.smith@happy.com
sad john.smith@lappy.com
paper mary.smith@happy.com
paper joy.smith@happy.com

What I would like to end up with is if I select project happy is:

jane.smith@happy.com;john.smith@lapp... th@happy.com

Any help would be greatly appreciated.

Thanks

re: Concatenate multiple rows into one string

On 18 Jun 2004 07:08:47 -0700, Smythe32@aol.com (Smythe32) wrote:

You'll have to do that with a recordset: open a recordset on a query
that pulls the right rows, then loop over it, and for each row add the
email address and a semicolon to a string variable. At the very end
remove the last semicolon.

-Tom.

[color=blue]
>Hi,
>
>I have the following table:
>
>Project Name email_addr
>happy jane.smith@happy.com
>happy john.smith@lappy.com
>happy mary.smith@happy.com
>sad john.smith@lappy.com
>paper mary.smith@happy.com
>paper joy.smith@happy.com
>
>What I would like to end up with is if I select project happy is:
>
>jane.smith@happy.com;john.smith@lappy.com;mary.sm ith@happy.com
>
>Any help would be greatly appreciated.
>
>Thanks[/color]

re: Concatenate multiple rows into one string

Tom van Stiphout <tom7744@no.spam.cox.net> wrote in message news:<57v5d0hmi8t1i82fpj9js86arojfilhaj6@4ax.com>. ..[color=blue]
> On 18 Jun 2004 07:08:47 -0700, Smythe32@aol.com (Smythe32) wrote:
>
> You'll have to do that with a recordset: open a recordset on a query
> that pulls the right rows, then loop over it, and for each row add the
> email address and a semicolon to a string variable. At the very end
> remove the last semicolon.
>
> -Tom.
>[/color]
There's an example of this here:
http://www.mvps.org/access/modules/mdl0004.htm
or here..
http://www.mvps.org/access/modules/mdl0008.htm

re: Concatenate multiple rows into one string

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0406181435.515eae4@posting.google.c om>...[color=blue]
> Tom van Stiphout <tom7744@no.spam.cox.net> wrote in message news:<57v5d0hmi8t1i82fpj9js86arojfilhaj6@4ax.com>. ..[color=green]
> > On 18 Jun 2004 07:08:47 -0700, Smythe32@aol.com (Smythe32) wrote:
> >
> > You'll have to do that with a recordset: open a recordset on a query
> > that pulls the right rows, then loop over it, and for each row add the
> > email address and a semicolon to a string variable. At the very end
> > remove the last semicolon.
> >
> > -Tom.
> >[/color]
> There's an example of this here:
> http://www.mvps.org/access/modules/mdl0004.htm
> or here..
> http://www.mvps.org/access/modules/mdl0008.htm[/color]
I am unable to get this code to work. I think it has something to do
with I am looking at a string instead of a long.

re: Concatenate multiple rows into one string

Smythe32@aol.com (Smythe32) wrote in message news:<7f494502.0407091004.3956cd9b@posting.google. com>...[color=blue]
> pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0406181435.515eae4@posting.google.c om>...[color=green]
> > Tom van Stiphout <tom7744@no.spam.cox.net> wrote in message news:<57v5d0hmi8t1i82fpj9js86arojfilhaj6@4ax.com>. ..[color=darkred]
> > > On 18 Jun 2004 07:08:47 -0700, Smythe32@aol.com (Smythe32) wrote:
> > >
> > > You'll have to do that with a recordset: open a recordset on a query
> > > that pulls the right rows, then loop over it, and for each row add the
> > > email address and a semicolon to a string variable. At the very end
> > > remove the last semicolon.
> > >
> > > -Tom.
> > >[/color]
> > There's an example of this here:
> > http://www.mvps.org/access/modules/mdl0004.htm
> > or here..
> > http://www.mvps.org/access/modules/mdl0008.htm[/color]
> I am unable to get this code to work. I think it has something to do
> with I am looking at a string instead of a long.[/color]

Sounds like you're not passing the right value to the function - if
you specify that the field is a string, it should add the delimiters
for you. Or of course, you could do as Tom suggested...