Combine querie-results into a single string

[drlandau]

Hi all,

Is there a way of combining the result from a querie into a single string?

I have a table with names and email addresses and I would like an easy way of exporting all the email adresses to a single string seperated with semicolons (";&quot so it is easy to cut'n'paste it into a mail and send it to everyone from the table that have an email address.

Anyone?

Thanks,
Nicolaj

 

[hsitz]

Instead of designing the query (in design view) with each field in a separate column, just type a single long string into one of the FieldName boxes. You probably won't be able to see the whole string at once (unless you right click on the fieldname box and click "Zoom&quot, but it will work:

[Field1] & "; " & [Field2] & "; " & [Field3] (and so on)

The above string, for example, would print field1 and field2 and field3 as a single long string separate with semicolons. -- Herb

 

[drlandau]

Thanks for the reply.

Maybe I didn't explain myself well enough.

Fields /values of TABLE1:

NAME ADDR PHONE EMAIL
Mr. A Abcdef 123 A@company.com
Mr. B Abcdef 123 B@company.com
Mr. C Abcdef 123 C@company.com
Mr. D Abcdef 123 D@company.com
Mr. E Abcdef 123 E@company.com
Ms. F Abcdef 123 F@company.com
Ms. G Abcdef 123 G@company.com
Ms. H Abcdef 123 H@company.com

What I wanted was a way of retreiving all the values stored in the field "EMAIL", in a query it would return 8 rows, each with an email address, but I would like somehow to add every row to the next so the actual output (maybe displayed in a field in a form) would be this:

A@company.com;B@company.com;C@company.com;D@company.com;E@company.com;F@company.com;G@company.com;H@company.com

Am I making any sense?

All the best,
Nicolaj

 

[mymou]

Hi Nicolaj

Sounds like this is a one off - if so, then just export and then use Excel (=A2&";" &B1).

Srew

 

[a9]

You could do it with a fairly simple piece of code. Create a new table, in this example it has two fields

LongEmail which is Memo format
Created which is Date format
the Table is named LEMail

Create this code

Function CreateLongEMail()
On Error GoTo Err_CreateLongEMail
Dim MyDb As Database, MyRst As Recordset, ResRst As Recordset, EMList As String, SQLString As String
Set MyDb = CurrentDb
SQLString = "SELECT STRUCTURE.EMail FROM STRUCTURE"
SQLString = SQLString & " WHERE (((STRUCTURE.EMail) Is Not Null));"
Set MyRst = MyDb.OpenRecordset(SQLString, dbOpenDynaset)
EMList = ""
With MyRst
.MoveFirst
EMList = !EMAIL
Do Until .EOF
EMList = EMList & ";" & !EMAIL
.MoveNext
Loop
.Close
End With
Set ResRst = MyDb.OpenRecordset("LEMAil", dbOpenDynaset)
ResRst.AddNew
ResRst!LongEmail = EMList
ResRst!created = Now()
ResRst.Update
ResRst.Close
Set MyDb = Nothing
MsgBox "EMail List Created", vbInformation + vbOKOnly, "Run complete"
Exit_CreateLongEMail:
Exit Function
Err_CreateLongEMail:
MsgBox Err.Description, vbCritical + vbOKOnly, "E-Mail Run Failed"
Resume Exit_CreateLongEMail
End Function


The SQLString will have to match your table and field names (TABLE1 and EMAIL?)

This will create the string you require. However it wll soon be extremely long. If you have problems viewing the data in the table select the field and Use Shift+F2 which will open the Zoom window.

 

[drlandau]

Thanks!

I'll test it as soon as I get the time

All the best
Nicolaj