e-mail list

[Kungen]

Hi!
I have a column with e-mail addresses. Some of the rows are NULL.
What can I do to retreive all the addresses in one single line,
separated with semicolons?
I know how to use calculated fields (like this + ' ; ' + that etc) but
how do I write a query that "knows" how many addresses there are, omits


NULL values and puts it all in one nice row?


Note that I have only one table, and only one column of data to be
retrieved. No relations whatsoever. Just this single column who's
content I need to get in a single row, separated by semicolons.


I'm not very knowledgeble, so I'd really appreciate if someone could
write a snippet of code for me.
I tried to excerpt what I needed from
http://www.mvps.org/access/modules/mdl0004.htm but I just couldn't do
it.


Kungen

 

[HS Hartkamp]

The code you found suits your purpose perfectly. If you cannot take bits
from it and get something working, take the whole lot and get that working.
Then, take bits out that you feel you don't need, and keep testing. That way
you'll learn what is happening.

If you want to omit null-values, you can either do:
- Change the second amperdsand (&) into a plus-sign (+) in row varConcat =
varConcat & rs(strFldConcat) & ";"
- Change this row to
if not IsNull(rs(strFldConcat))
varConcat = varConcat & rs(strFldConcat) & ";"
End If

The second one is obvious: if you don't get a null-value, it's added to the
string. (i.e. do not add null-values)
The first one is a shortcut which is not so easy to read: use it in private,
but when more programmers are working this code, better not use it. To
concatenate two strings, you'd normally use &. If you use + then Access will
literally add the two strings getting the same result in most cases. In case
of a null-value, the result of concatenation is just the (other) value, but
in artihmetic adding, null + sometring results in null. This way, the
semicolon is omitted when the field contains nothing.

Bas Hartkamp.