I have a some sample VBA code at http://forestbyte.com/vba-code-samples/ - look for JoinFromArray function. You can call that from any query. I am also pasting the code here (add it to a standard module):
Code:
Attribute VB_Name = "modJoinFromArray"
Option Compare Database
Option Explicit
Public Function vcJoinFromArray(vID, sUniqueIDName As String, sIDDataType As String, sQueryName As String, sFieldNames As String, sFieldNamesDelimiter As String, Optional sJoinDelimiter As String)
Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String
Dim sFields() As String, strSelectedFields, iCount As Integer, vChar
Dim arrData As Variant
Set db = CurrentDb
sFields = Split(sFieldNames, sFieldNamesDelimiter)
For iCount = 0 To UBound(sFields)
strSelectedFields = strSelectedFields & "[" & sQueryName & "].[" & sFields(iCount) & "],"
Next iCount
If Right(strSelectedFields, 1) = "," Then strSelectedFields = Left(strSelectedFields, Len(strSelectedFields) - 1)
If sIDDataType = "TEXT" Then vChar = Chr(39)
If sIDDataType = "NUM" Then vChar = ""
If sIDDataType = "DATE" Then vChar = Chr(35)
strSQL = "SELECT " & strSelectedFields & " FROM [" & sQueryName & "] WHERE [" & sQueryName & "].[" & sUniqueIDName & "] =" & vChar & vID & vChar & ";"
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount = 0 Then
vcJoinFromArray = ""
Exit Function
End If
With rst
.MoveLast
.MoveFirst
arrData = .GetRows(.RecordCount)
End With
arrData = TransposeArray(arrData)
arrData = vcConvertToOneDimArray(arrData)
Dim x
x = sJoinDelimiter
vcJoinFromArray = Join(arrData, IIf(sJoinDelimiter = "", " | ", sJoinDelimiter))
Set rst = Nothing
Set db = Nothing
End Function
Function TransposeArray(v As Variant) As Variant
' Transpose dimensions of a 0-based
' multi-dimensional array
Dim x As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant
Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim tempArray(Xupper, Yupper)
For x = 0 To Xupper
For Y = 0 To Yupper
tempArray(x, Y) = Trim(v(Y, x))
Next Y
Next x
TransposeArray = tempArray
End Function
Function vcConvertToOneDimArray(a As Variant) As Variant
Dim row As Integer, col As Integer
Dim Temp As String
Dim tempArray() As Variant, Xupper As Long, Yupper As Long
ReDim tempArray(UBound(a))
For row = LBound(a) To UBound(a)
Temp = ""
For col = LBound(a, 2) To UBound(a, 2)
Temp = Temp & " " & Trim(a(row, col)) & " "
Next col
tempArray(row) = Trim(Temp)
Next row
vcConvertToOneDimArray = tempArray
End Function
Cheers,
Vlad