Hi,
I use the following code in a query...
SELECT [tblData].[ID], Conc("Field1","ID",[ID],"tblData") AS Field1, Conc("Field2","ID",[ID],"tblData") AS Field2
FROM tblData
GROUP BY [tblData].[ID];
but I want to change the [ID] field from number to text..
so how to change the code?
I use the following code in a query...
SELECT [tblData].[ID], Conc("Field1","ID",[ID],"tblData") AS Field1, Conc("Field2","ID",[ID],"tblData") AS Field2
FROM tblData
GROUP BY [tblData].[ID];
but I want to change the [ID] field from number to text..
so how to change the code?
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