I dont use ADO so heres a DAO version.
Code:
Public Function ConCatStr(strSql As String, fld As String) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strOut As String
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)
If rs.BOF And rs.EOF Then
ConCatStr = ""
GoTo MyExit
End If
Do Until rs.EOF
ConCatStr = ConCatStr & rs(fld) & vbNewLine
rs.MoveNext
Loop
MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
The query would look like
Code:
SELECT DISTINCT TempOrderImport.INVNO, ConCatStr("Select ITEM2 from TempOrderImport where INVNO = '" & [INVNO] & "'","ITEM2") AS Expr1
FROM TempOrderImport;
note that you need to expand the height of the query grid to see the results.