concatenate problem

[Jackie]

I followed a link (in this forum) to the following code to concatenate
multiple fields onto one line, separated by commas. It works perfectly as
along as the field I'm trying to combine is a Number (double). The problem
is I am pulling this info from a third party database that can't be changed.
Even though the info in this particular field is numeric, it has a data type
of "text".

I won't be the one running the reports, so I need the code to combine the
based on a text field rather than numeric. The code is posted below. What
do I need to change to accomplish this? Thanks.

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

 

[Duane Hookom]

Use syntax like:
FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE txtFamID =
""" & [txtFamID] & """")

 

[Jackie]

Thank you SOOO much! You're a genius! I knew someone who knew what they
were doing could answer this in their sleep! Thanks again!

Use syntax like:
FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE txtFamID =
""" & [txtFamID] & """")

--
Duane Hookom
MS Access MVP
--

I followed a link (in this forum) to the following code to concatenate
multiple fields onto one line, separated by commas. It works perfectly as
along as the field I'm trying to combine is a Number (double). The
problem
is I am pulling this info from a third party database that can't be
changed.
Even though the info in this particular field is numeric, it has a data
type
of "text".

I won't be the one running the reports, so I need the code to combine the
based on a text field rather than numeric. The code is posted below.
What
do I need to change to accomplish this? Thanks.

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