Concatenate - Error

  • Thread starter Thread starter Karl
  • Start date Start date
K

Karl

I am trying to Concatenate some rows in access to columns. I tried a
previously provided answer on the message board, but I keep getting an error.

“Undefined function ‘concatenate’ in expressionâ€

I am using the following select query:

SELECT Languages.AddressID, Concatenate("SELECT description FROM
tbllanguages WHERE addressID =""" & [addressID] & """") AS [Language]
FROM Languages;

I copied the module from the following:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

This is how my data is now:

AddressID Description
33402 Spanish
36112 French
36112 Spanish
36112 Portuguese

And I am trying to reflect this:

AddressID Description
33402 Spanish
36112 French, Spanish, Portuguese


Any help would be great

Karl
 
You must also copy the module basConcatenate into your own MDB (or ACCDB)
database file. This module contains the following function:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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
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
Concatenate = strConcat
End Function


Notice that this function can use either ADO or DAO; all you have to do is
to comment / uncomment the relevant lines before using it.
 
AND make sure you save the module with a name that is not the name of
any procedure. That is don't save the module with the name Concatenate
- use basConcatenate or modConcatenate or myUsefulFunctions or ...

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I did copy the modue successfully and named it basConcatenate. I then tried
placing brackets around the field [desription] in my string and that sovled
the issue, thanks for your suggestions.
 
Back
Top