[AccessD] Concatenating data into lists

Hi David

Or adjust this variation with a saved query which I posted recently:

Here's a variation where you save the SQL to look up the Qualifications in a separate query assuming that your primary key, VolNum, is a number:

  PARAMETERS lngKey Long;
  SELECT 
    QualificationCode
  FROM
    tblQualifications
  WHERE
    VolNum = lngKey
  ORDER BY
    QualificationCode;

Save this as, say, qdyQualifications.

Now, adjust you main query to include the function below to look something like this:

  SELECT
    *,
    ConcatenateRecords('qdyQualifications',[VolNum],'QualificationCode',', ')
  FROM
    tblVolunteers;

/gustav

<code>

Public Function ConcatenateRecords( _
  ByVal strSource As String, _
  ByVal lngKey As Long, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from one field (strField) from all
' records in query strSource using parameter value lngKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 1999-10-12. Cactus Data ApS, CPH

  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field
    
  Dim booPluralis As Boolean
  Dim strFields   As String
  
  On Error GoTo Err_ConcatenateRecords
  
  Set dbs = CurrentDb()
  
  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = lngKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)
    
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With
    
    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If
  
  Set dbs = Nothing
  
  ConcatenateRecords = strFields
  
Exit_ConcatenateRecords:
  Exit Function
  
Err_ConcatenateRecords:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecords
  
End Function

</code>