Formatting text

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Please help !!

If you have a query which produces say 5 records (just one field),

eg John Smith
James Doe
Brian Smith
Jane Seymour
Mike Chilvers

and you want to print these on a report ON THE SAME LINE, ie John Smith,
James Doe - delimited with commas

how is this done??

PLEEEASE HELP


Cheers

Jake
 
Jacob,
Paste this into a module:

Public Function FillControl()

' Will fill an unbound control with a list of names from
' each record in one table field
' i.e. John Smith, Joe Jones, Henry Adams
' without a comma after the final name.

Dim db As Database
Dim rstTemp As Recordset
Dim F As Field
Dim FillString As String

Set db = CurrentDb
Set rstTemp = db.OpenRecordset("YourTableNameHere")
Set F = rstTemp("TheFieldName")

With rstTemp
Do While Not rstTemp.EOF
FillString = FillString & F & ", "
.MoveNext
Loop
End With
FillControl = Left(FillString, Len(FillString) - 1)

rstTemp.Close
Set db = Nothing

End Function

Then, on your form or report, set the control source of an unbound control
to:
=FillControl()

Make sure you size the control wide enough to display the entire string.
Change the table and field names above to whatever your actual table and
field names are.
 
Thanks again for your help Fred, but my problem continues !!!

Lets say I have a query which produces employee dept data - there are 2
fields output from this query: ie

dept and empName

On my report, I am grouping by dept, and underneath each grouping (ie
underneath each dept name) I want to write a list of all of the empNames
separated by commas.

Now the code which you have supplied DOES list the empNames separated by
commas, BUT it lists ALL of the empNames in the whole of the query, NOT just
the empNames applicable to the relevant dept

Thus, if dept '102' appears 5 times (5 employees), I want those 5 empNames
to be written (separated by commas) underneath the dept on the report, and
so on for the next dept.

At the minute, ALL employees are being output under EACH dept grouping.

I hope this makes sense, thank you for your help so far, much appreciated

Cheers

Jake.
 
Alan,
Regarding:
I have a Form with one unbound control I set the event to
Click to expand...
click = FillControl() <

There is no "event" for this!

Set the ** Control's ** Control Source to:
=FillControl()

If that still doesn't fill in the control, then place a code break on the
set Db = CurrentDb line and step through the code line by line.
If you place your cursor over the F and over FillString you can read each
value as you step through. See what happens.
 
Back
Top