Concatenate multiple rows

[frankt68]

Hi!
I have a problem using the following function (found it here on forum) in Access 2010, which works very well in Access 2000.


Option Compare Database
Option Explicit

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function

When I run this query

SELECT [qryOne].[ID], Conc("Field1","ID",[ID],"qryOne") AS Field1, Conc("Field2","ID",[ID],"qryOne") AS Field2
FROM qryOne
GROUP BY [qryOne].[ID];

in Access 2010, I get an error with the following message:

Run-time error '-2147217900 (80040e14)':
Syntax error (missing operator) in query expression >> [ID] = ".

and function stops at line

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly


Since I have very little experience using Access, I ask for a clear advice on how can I resolve this problem.