C
Cydney
This question for Duane Hookum:
I am using your concatenation function in my access database. My problem is
that the parts that are combined equal more than 255 characters. It's cutting
off the text even though the field that I am appending into is a Memo field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).
Your function:
Option Compare Database
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
I am using your concatenation function in my access database. My problem is
that the parts that are combined equal more than 255 characters. It's cutting
off the text even though the field that I am appending into is a Memo field
type. Any ideas of a way I can fix this? I need to also export this
information to Excel (2003).
Your function:
Option Compare Database
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