Concatenate function problem

[Matt18]

Hi

I have a database with customer ID and their answer as shown below. What I would like to do is create a query that will group by ID and show Answer separated by comma.


Table

ID Answer
1 a
1 b
2 a
2 b
5 a
5 b

Query
1 a,b
2 a,b
5 a,b

Access does not support GROUP_CONCAT I think so I looked for a function that would do that for me.

I found a sample database (old one) in .mdb format that does that perfectly. Function is pasted bellow. But as I copy the module to my database (.mdb or .accdb), I get error:

Compile error:
User-defined type not defined

I have "Microso... 14.0 Access database engine Object Library" checked under tools and references

Code from the module is:

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

Thank you very much for your help in advance!

 

[MarkK]

This code requires a reference to ADODB, which is called 'Microsoft ActiveX Data Objects 2.X Library' in your references dialog.

 

[vbaInet]

Here's a commonly known url for this:

http://allenbrowne.com/func-concat.html

You may need the DAO reference though