|
|
OT: Microsoft Access
|
Does anyone know how I can take data from a query and combine all the
records found in one field into a single string or destination field? I
need to make a report which shows invoice numbers taken from a table (in a
select query) and displays them all in a single line. At the moment I have
a table in the report which does the job but it doesn't look very good when
there is lots of invoices as they are displayed vertically (I have this in
|
*are* lots of invoices. Doh!
|
Simple way, create a new field and use an update query to combine all
the cells that you want into the new field.
|
Thanks. The problem is I don't want to combine cells per se, I need to
combine records from the same field (IYSWIM). All the values I'm trying to
combine are found in the same column if viewed as a datasheet. Maybe I
misunderstood you but I thought you thought I meant I was trying to combine
values from different fields in a table.
|
|
|
the report header).
|
Apart from using some VBA script, you can display a query in a pivot view
with fields as columns.
or VB script like:
Sub InvoicesInARow()
Dim rs As New ADODB.Recordset
Dim sInvoiceNumbers As String
rs.Open "InvoiceTable", MyConnection, adOpenStatic, adLockReadOnly
sInvoiceNumbers = ""
Do While Not rs.EOF
sInvoiceNumbers = sInvoiceNumbers & ";" & rs("InvoiceNumber")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Debug.Print sInvoiceNumbers
End Sub
|
Superb! Thanks very much.
|
|
|
|