Hi all,

I'm trying to use a VBA functtion to join multiple records into one new field of a query but it takes ages to finish does not always work probably. Can anyone help me with that?


The function is exuctued from this SQL-query:

SELECT LEVEL1, LEVEL2, LEVEL3, LEVEL4, JoinFields, Count(INCIDENT_ID), ("CALLBACK_REASON","[IM_Calls-Incidents]","LEVEL1='" & [LEVEL1] & "' AND LEVEL2='" & [LEVEL2] & "' AND LEVEL3='" & [LEVEL3] & "' AND LEVEL4='" & [LEVEL4] & "') AS Comment,
FROM [IM_Calls-Incidents] GROUP BY [...]


Now the function:

Function JoinFields(F As String, T As String, W As String)
Dim TempTable As Recordset
Dim Result As Variant
Dim ResultField As String
Dim i,j As Integer

Set TempTable = CurrentDb().OpenRecordset("SELECT " & F & " FROM " & T & " WHERE " & W)
TempTable.MoveLast
j = TempTable.RecordCount
TempTable.MoveFirst
Result = TempTable.GetRows(j)

For i = 0 To j - 1
ResultField = ResultField & Result(0, i) & " | "
Next i

JoinFields = Left(ResultField, Len(ResultField) - 3)
End Function


Thanks a lot in advance,
Ulf