concatenating problem

[Jackie]

I followed a link (in the reports forum) to 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 actually 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
data based on a text field rather than numeric. The code craps out on
"rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic".
The module is pasted below. What do I need to change to make it combine
alpha rather than numeric fields? 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

 

[Douglas J. Steele]

I don't see any reason why the routine shouldn't work with text.

What are you passing as pstrSQL?

 

[Randy Harris]

I followed a link (in the reports forum) to 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 actually 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
data based on a text field rather than numeric. The code craps out on
"rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic".
The module is pasted below. What do I need to change to make it combine
alpha rather than numeric fields? 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

As Doug said, this should work if you pass a valid SQL to it. You might
want to take a look at the ADO GetString method. It does exactly what this
function does.

 

[Douglas J. Steele]

As Doug said, this should work if you pass a valid SQL to it. You might
want to take a look at the ADO GetString method. It does exactly what
this
function does.

I'm not sure it does, Randy.

GetString returns the entire recordset. I believe Duane's function is
intended to concatenate all values with a similar key into one field, so
that if you have:

Doug Blue
Doug Red
Doug Green
Randy Yellow
Randy Blue

you can get

Doug Blue, Red, Green
Randy Yellow, Blue

 

[Jackie]

I posted this in the reports forum, too, and Duane Hookam (the gentleman who
wrote the code) answered me. The answer is:

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

Thank you for you help ... I appreciate it very much