Comma seperated query query (Newbie)

[Martin Osborne]

Hello All,

I've made a query that returns a list of people's names against certain
actions. The list of names can be up to fifteen long and equally it could be
blank. I would like a comma (and a space) to separate where there are names
but don't want a comma to appear when there isn't a name (or at the end of
the list).

I've had several attempts with "IIf" but can't seem to make it work. Any
suggestions would be very gratefully received.

Many thanks,

Martin

 

[Marshall Barton]

I've made a query that returns a list of people's names against certain
actions. The list of names can be up to fifteen long and equally it could be
blank. I would like a comma (and a space) to separate where there are names
but don't want a comma to appear when there isn't a name (or at the end of
the list).

Duane has a function that build a list like that:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[Marshall Barton]

Duane has a function that build a list like that:

http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

Please keep the correspondence in the newsgroup.

Response to private email:

Yes, this is acustom function.

No, it is not limited to one computer.

Since the function only uses ordinary Access features, once
you include the function in a standard module in your
application, it will work on any machine that your
appication can run on.

 

[Martin Osborne]

Hello Marshall,

Once again thanks for the help and I apologise for misdirecting the mail!

I understand your point regarding the advice you've given me and I shall try
and incorporate it into my database.

Can I assume from what you're saying there is no other way of producing the
same effect without this custom function?

Regards,

Martin

 

[Marshall Barton]

I think there may be a way to create that kind of list using
SQL, but it's beyond my skill level.

Using a custom function (called User Defined Function or
UDF) is a **very** common thing to do in Access. The only
reason to not use them is a potential future move to a
different database engine such as Oracle or SQL Server.
Even in that case you will still need to create something in
those environments to create the list, but you would also
have to convert lots of other things too.