我在寻找一个Access 2007等效于SQL Server的COALESCE函数。

I'm looking for an Access 2007 equivalent to SQL Server's COALESCE function.

在SQL Server中，你可以这样做：

In SQL Server you could do something like:

人

John
Steve
Richard

SQL

DECLARE @PersonList nvarchar(1024)
SELECT @PersonList = COALESCE(@PersonList + ',','') + Person
FROM PersonTable

PRINT @PersonList

主要生产：约翰，史蒂夫，理查德

Which produces: John, Steve, Richard

我想这样做，但在Access 2007。

I want to do the same but in Access 2007.

有谁知道如何结合行这样在Access 2007？

Does anyone know how to combine rows like this in Access 2007?

推荐答案

下面是一个简单的用户定义函数（UDF）和可能的使用。

Here is a sample User Defined Function (UDF) and possible usage.

功能：

Function Coalsce(strSQL As String, strDelim, ParamArray NameList() As Variant)
Dim db As Database
Dim rs As DAO.Recordset
Dim strList As String

    Set db = CurrentDb

    If strSQL <> "" Then
        Set rs = db.OpenRecordset(strSQL)

        Do While Not rs.EOF
            strList = strList & strDelim & rs.Fields(0)
            rs.MoveNext
        Loop

        strList = Mid(strList, Len(strDelim))
    Else

        strList = Join(NameList, strDelim)
    End If

    Coalsce = strList

End Function

用法：

SELECT documents.MembersOnly, 
    Coalsce("SELECT FName From Persons WHERE Member=True",":") AS Who, 
    Coalsce("",":","Mary","Joe","Pat?") AS Others
FROM documents;

这是ADO的版本，灵感来自一个注释onedaywhen

An ADO version, inspired by a comment by onedaywhen

Function ConcatADO(strSQL As String, strColDelim, strRowDelim, ParamArray NameList() As Variant)
   Dim rs As New ADODB.Recordset
   Dim strList As String

   On Error GoTo Proc_Err

       If strSQL <> "" Then
           rs.Open strSQL, CurrentProject.Connection
           strList = rs.GetString(, , strColDelim, strRowDelim)
           strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
       Else
           strList = Join(NameList, strColDelim)
       End If

       ConcatADO = strList

   Exit Function

   Proc_Err:
       ConcatADO = "***" & UCase(Err.Description)
   End Function

从：http://wiki.lessthandot.com/index.php/Concatenate_a_List_into_a_Single_Field_%28Column%29