ms访问 – Microsoft Access在表中压缩多行

Public Function GetList(SQL As String _,Optional ColumnDelimeter As String = "," _,Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
'   1. SQL is a valid Select statement
'   2. ColumnDelimiter is the character(s) that separate each column
'   3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)

Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String

On Error GoTo ProcErr

Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)

sResult = oRS.GetString(adClipString,-1,ColumnDelimeter,RowDelimeter)

If Right(sResult,Len(RowDelimeter)) = RowDelimeter Then
    sResult = Mid$(sResult,1,Len(sResult) - Len(RowDelimeter))
End If

GetList = sResult
oRS.Close
oConn.Close

CleanUp:
    Set oRS = Nothing
    Set oConn = Nothing

Exit Function
ProcErr:
    ' insert error handler
    Resume CleanUp

End Function

Remou的版本具有添加的功能,您可以传递一个值数组,而不是SQL语句.

示例查询可能如下所示：

SELECT SourceTable.Name,GetList("Select Day From SourceTable As T1 Where T1.Name = """ & [SourceTable].[Name] & """","",",") AS Expr1
FROM SourceTable
GROUP BY SourceTable.Name;