Taking all values from a column and putting them together with a "; " separator

[Dave DeSteno]

It seemed simple at the time...

Ask for their email addresses, then when I have a list of people to contact, I can make my Access database spit the addresses out for me...

Here's my quandry:
I have a table (related to other tables in my database)that simply lists [Name][EmailAddress] and [ContactNumber]. I use a few queries to sort everything out and end up with usually about 5-10 people listed that need an email to remind them of delinquent entries. I have an Outlook template all made up and ready - and I figured I could grab the email addresses pretty easily (never tried this before - but hey - Access should make this part easy, right?)

After learning that there doesn't seem to be a built in function for this, I figured maybe I should try to send these addresses to a textbox in my form (hidden from view) and just use vba to plug that value into the To: line...

After a LOT of frustration, I am stumped. I know it's probably something simple - but for the life of me, I cannot figure this out! All of the threads I read are either for how to build an email in vba (which I already can do) or how to concatenate different values on a row...

I need (I think) to be able to concatenate the values from one COLUMN in my query into a textbox, and I can carry it from there.

(My SQL and VBA skills are amateur, so if you have the time to respond, please explain it to me like I'm a 10 year old!)

Thanks in advance for your help!

Dave

[hype261]

So basically you are going to have to run some VBA to get this to work, but it shouldn't be that hard.


First you are going to have to open a recordset to get all the emails. Then you are going to have to loop through the recordset.

 
dim rcd as DAO.recordset
dim emailAddress as string
 
set rcd = CurrentDB.OpenRecordset("SELECT EmailAddress FROM SomeTable")
 
do until rcd.eof
emailAddress = emailAddress & rcd!EmailAddress & "; "
loop
 
'Make sure we have some email addresses
if(Len(emailAddress) > 0 )
 
'Do something interesting
 
Else
 
'Report to user no email addresses
 
End if
 

This should get you started.

[Mc1brew]

Does t-sql work in access? I'v seen this done with an elaborate use of coalesce and stuff, though I've seen, I could not tell how to repeat.

[Lysander]

Interested in the question. Fairly easy to do in code but can it not be done via PIVOT or TRANSFORM? I can't work out how, but it seems like it should be possible.

[hype261]

T-SQL doesn't work with access (I really wish it would). Also on my list would be stored procedures, but none the less.

I suppose you could do a combination of a VBA function inside the SQL.

[NeoPa]

Check out Combining Rows-Opposite of Union. It's not perfect but fundamentally does the job.

[Dave DeSteno]

I thank you all for the replies.

Sadly, I still cannot seem to make this work.

I'll keep plugging along - it's gotta break eventually for me, right?

I really would need it spelled out for me, I suppose - I wasn't sure what to do with the code above from hype261. Sorry to be dense! If someone has the time to hold me hand through this, fantastic - if you don't, that's fine as well.
The query name I need the info from is "qryRespCount2" and the field is "ResponsibleEmail"
I launch a form that runs 2 queries (qryRespCount1 and qryRespCount2) These in turn give me a list of names (responsible parties that have yet to sign off on jobs) and the count of open jobs per person - that display on a continuous form. My goal was to have a command button at the bottom of that form that would open an Outlook email (or better yet, an Outlook Template) and populate the "To:" field on that email with all of the addresses of the people with delinquent jobs.

Again, if someone has the time to spell it out, I thank you in advance. If not, thank you for your help up until now!

Dave

[NeoPa]

Dave, I will see what I can do to help you through this. No promises at this time though.

1. What's the name of the form you want this data to display on?
2. What is the SQL of the recordsource of this form?
3. What is the name of the control on the form where you would like the list to display?

[Dave DeSteno]

Okay - I was off on my Query/Form names slightly...
They are: ResponsibleCountQuery1 and ResponsibleCountQuery2 (I know - this was BEFORE I read about normalization)
The field name is still [ResponsibleEmail]

1. Form name to display data: ResponsibleCountForm1

2. Here's the SQL for the recordset:
SELECT ResponsibleCountQuery2.Responsible, ResponsibleCountQuery2.CountOfResponsible, ResponsibleCountQuery2.ResponsibleEmail, ResponsibleCountQuery2.RoundDate
FROM ResponsibleCountQuery2;

This is a continuous form - the text box I want to write to is in the Form Footer. (again, if this is not a good plan, let me know!)

3. Text24 (on ResponsibleCountForm1 - in the footer)
I would like the values displayed (I guess) in a text box on the very same form - ultimately I want to use SendObject - or better yet, plug it into the "To:" line on an outlook template... SendObject is UGLY when it sends out.

There has to be a better way, right?

The queries take the responsible people, and give me a count of who still has how many jobs delinquent. I then want to generate an email with the click of a button [Command26] to only the people listed them reminding them about it. (yeah - I'm not making friends around here...)

Thanks in advance
Dave

[NeoPa]

Well, this is somewhat different from what I expected, but you have answered the questions clearly so let's see where we can go with it. I'm no longer of the opinion that my previous idea will help you much.

As an alternative, it seems we would probably be looking at deciding on a trigger, and developing some code to determine the value when triggered.

There are many Form based events to choose from, but my best guess would be the Load event would suit you best for this.

At that point the code could process through the recordset concatenating all the values together separated by "; "s. As there is no need to move through the Recordset on the form itself, the .RecordsetClone property is probably the one to use.

The code below illustrates the kind of routine you're looking for :

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
    Dim strList As String
 
    With Me.RecordsetClone
        Do While Not .EOF
            strList = strList & "; " & .ResponsibleEmail
            Call .MoveNext
        Loop
        Me.Text24 = Mid(strList, 3)
    End With
End Sub

[Dave DeSteno]

This works beautifully! I owe you a pint!

Thank you. Thank you. Thank you!

[NeoPa]

I don't mind if I do :-D

You're welcome of course.