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
I think Nz is what you\'re after, syntax is Nz(variant, [if null value])
. Here\'s the documentation link: Nz Function
---Person---
John
Steve
Richard
DECLARE @PersonList nvarchar(1024)
SELECT @PersonList = Nz(@PersonList + \',\',\'\') + Person
FROM PersonTable
PRINT @PersonList
Although Nz does a comparable thing to COALESCE, you can\'t use it in Access to do the operation you are performing. It\'s not the COALESCE that is building the list of row values, it\'s the concatenatiion into a variable.
Unfortunately, this isn\'t possible inside an Access query which has to be a single SQL statement and where there is no facility to declare a variable.
I think you would need to create a function that would open a resultset, iterate over it and concatenate the row values into a string.
To combine rows in Access, you\'ll probably need code that looks something like this:
Public Function Coalesce(pstrTableName As String, pstrFieldName As String)
Dim rst As DAO.Recordset
Dim str As String
Set rst = CurrentDb.OpenRecordset(pstrTableName)
Do While rst.EOF = False
If Len(str) = 0 Then
str = rst(pstrFieldName)
Else
str = str & \",\" & rst(pstrFieldName)
End If
rst.MoveNext
Loop
Coalesce = str
End Function
You\'ll want to add error-handling code and clean up your recordset, and this will change slightly if you use ADO instead of DAO, but the general idea is the same.
I understand here that you have a table \"person\" with 3 records. There is nothing comparable to what you describe in Access.
In \"standard\" Access (DAO recordset), you will have to open a recordset and use the getrows method to have your data
Dim rs as DAO.recordset, _
personList as String, _
personArray() as variant
set rs = currentDb.open(\"Person\")
set personArray = rs.getRows(rs.recordcount)
rs.close
once you have this array (it will be bidimensional), you can manipulate it to extract the \"column\" you\'ll need. There might be a smart way to extract a one-dimension array from this, so you can then use the \"Join\" instruction to concatenate each array value in one string.