Combining fields into one

[Jerry Crosby]

I have a table with a field for say, last names. And say record 1 has
"Smith" in the last name field, Record 2 has "Jones" in the last name field,
etc.

In a report, I'd like to list all the last names I have in that table, one
right after the other. e.g., "Smith, Jones, Washington, Carver, etc."

I suspect it will take some coding, but regardless, how can I do it that
way?

Thanks in advance.

Jerry

 

[Geof Wyght]

Jerry,
I think it will require code.
Dim strNames As String
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.SQL = "Select CarrierName from 2Carrier"
Set rst = qdf.OpenRecordset()
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
strNames = strNames & rst.Fields("CarrierName") & ", "
rst.MoveNext
Loop
End If
'Remove the last comma & space.
strNames = Left(strNames, Len(strNames) - 2)
Set qdf = Nothing
Set rst = Nothing

I tested this and it works. Good luck.
Geof Wyght

 

[Jerry Crosby]

Thanks, Geof, but where would I put this code if I wanted the information
for printing on a report? How could I get it into a text box? Or is there
some other way?

Thanks in advance.

Jerry

 

[Geof Wyght]

Jerry,
I believe you would create an unbound text box on the
form. Then on the On Open event of the report you'd run
that code. Finally, still in the On Open event you'd say:
Me.txtNames = strNames
Hope that helps. If not just re-post and I'll keep an eye
out.
Geof Wyght

 

[Jerry Crosby]

Geof, sorry to bother you again.

I had put your code in the On Open event of the report and created an
unbound text box and named it "AllNames", but it stopped there.

For one thing, I think I need to change a line in your code:

You wrote:

qdf.SQL = "Select CarrierName from 2Carrier"

But I think I need to change the "Select CarrierName from 2Carrier" right?

I'm sorry, but I don't understand what that's doing.

Then, I did put Me.AllNames=strNames at the end of the code, but it errors
out. Says I cannot assign a value to this object.

I appreciate your help for this feeble mind!

Jerry

 

[Geof Wyght]

Jerry,
Sorry - I just made up a Select query with names that I'm
currently working with. Your last names must stored in a
table, no? Say the table name is tblNames, and the last
names field name is LastName. Then your query should look
like:
Select LastName From tblNames Order By LastName

As for the On Open event of the report, I'll test the
code in a database and get back to you in the morning.
Geof Wyght

 

[Geof Wyght]

Jerry,
I remember now that text boxes are problematic in reports.
Labels behave better. I tested this code this morning and
it worked:
Private Sub Report_Open(Cancel As Integer)
Dim strNames As String
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.SQL = "Select LastName from tblNames"
Set rst = qdf.OpenRecordset()
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
strNames = strNames & rst.Fields("LastName") & ", "
rst.MoveNext
Loop
End If
'Remove the last comma & space.
strNames = Left(strNames, Len(strNames) - 2)
'If this string is very long you won't be able to assign
the string to the caption.
Me.lblNames.Caption = strNames
Set qdf = Nothing
Set rst = Nothing
End Sub

Geof Wyght

 

[Jerry Crosby]

That did the trick, Geof! Thanks for your persistence.

Jerry