I'm looking for an Access 2007 equivalent to SQL Server's COALESCE function.
In SQL Server you could do something like:
Person
John
Steve
Richard
SQL
DECLARE @PersonList nvarchar(1024)
SELECT @PersonList = COALESCE(@PersonList + ',','') + Person
FROM PersonTable
PRINT @PersonList
Which produces: John, Steve, Richard
I want to do the same but in Access 2007.
Does anyone know how to combine rows like this in Access 2007?
Here is a sample User Defined Function (UDF) and possible usage.
Function:
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
Usage:
SELECT documents.MembersOnly,
Coalsce("SELECT FName From Persons WHERE Member=True",":") AS Who,
Coalsce("",":","Mary","Joe","Pat?") AS Others
FROM documents;
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
From: http://wiki.lessthandot.com/index.php/Concatenate_a_List_into_a_Single_Field_%28Column%29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With