Concatenate - Error

[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

 

[Sylvain Lafontaine]

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.

 

[John Spencer]

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
'====================================================

 

[Karl]

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.