Generate String and output to textbox in report

[Brooke]

I am new to access, so please excuse ignorance. Within my table
(contacts) I have a text field (other). What I am trying to do is
collect all of the text input from the field 'other', separate each
string by commas, and output to a single textbox on my report
(othertext). Normally...I would go in and clean up the data or assign
numbers to the text values, but in this instance that is counter
productive. Can anyone help me with code to generate a string and then
output it to a single text box in my report?

Many thanks in advance!

Brooke

[Ronald Roberts]

Brooke wrote:

I am new to access, so please excuse ignorance. Within my table
(contacts) I have a text field (other). What I am trying to do is
collect all of the text input from the field 'other', separate each
string by commas, and output to a single textbox on my report
(othertext). Normally...I would go in and clean up the data or assign
numbers to the text values, but in this instance that is counter
productive. Can anyone help me with code to generate a string and then
output it to a single text box in my report?

Many thanks in advance!

Brooke

Look up concatenation in Help.

Answer = Other1 & ", " & Other2

Ron

[Brooke]

That would work if I was using two seperate fields...and wanting to
join them together. Sorry if I wans't clear. I have a table where users
enter text in a text field. Each user's entry is stored as an entry in
my table...under the same field name. What I am trying to do is take
all of the data that was ever entered into that one field, and spit it
out to a single text box in a form. Right now, my report will display
data from the table, but creates new reports for each instance of data
in the 'other' field. I need all instances on one report.

Thanks though!

[pietlinden]

oh, in that case, you have to use code to do it.

Public Function DelimitedListOf Values(ByVal strTable As String, ByVal
strField As String) As String
Dim strTemp As String
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0). OpenRecordset(" SELECT [" & strField & "]
FROM " & strTable & " ORDER BY [" & strField & "];")
Do Until rs.EOF
strTemp = strTemp & rs.Fields(strFi eld) & ", "
rs.MoveNext
Loop

strTemp = Left$(strTemp, Len(strTemp) - 2)

rs.Close
Set rs = Nothing

DelimitedListOf Values = strTemp
End Function

Then you'd have to call the function in the rowsource for your unbound
textbox on your report. (too many prepositional phrases in that!!!
PHew!)