Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combine querie-results into a single string

Status
Not open for further replies.

drlandau

IS-IT--Management
Jul 20, 2000
36
DK
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 (";") 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
 
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"), 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


 
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
 
Hi Nicolaj

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

Srew
 
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.
 
Thanks!

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

All the best
Nicolaj
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top