I found a function to concatenate the results of a sql query. I want to use it as an aggregate string function in a sql statement. The function is:
module: 'modConcat' code:
------------------------------------------------
-------------------------------------------------------------
The sample sql statement I am trying to execute is:
SELECT DISTINCT [Name], Concat("SELECT Position FROM BoardMembers WHERE Name='"+[Name]+"' ",'') AS Positions
FROM BoardMembers;
When I use that sql in a query in access, I get the error:
Method 'Open' of object '_Recordset' failed
And when I tuse the sql call on my asp webpage, I get the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Concat' in expression.
The database works fine, and the sql is tracing correctly. My ultimate goal is to use this Concat function on from a sql statement on the webpage, but I get the undefined function error. Do you know how to make the function call work?
module: 'modConcat' code:
------------------------------------------------
Code:
Option Compare Database
Option Explicit
Public Function Concat(pstrSQL As String, Optional pstrDelim As String = ",") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
'MsgBox pstrSQL
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concat = strConcat
End Function
The sample sql statement I am trying to execute is:
SELECT DISTINCT [Name], Concat("SELECT Position FROM BoardMembers WHERE Name='"+[Name]+"' ",'') AS Positions
FROM BoardMembers;
When I use that sql in a query in access, I get the error:
Method 'Open' of object '_Recordset' failed
And when I tuse the sql call on my asp webpage, I get the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Concat' in expression.
The database works fine, and the sql is tracing correctly. My ultimate goal is to use this Concat function on from a sql statement on the webpage, but I get the undefined function error. Do you know how to make the function call work?
Last edited: