#Name? using Concatenate code created by Duane Hookom 2

[Eprice]

Hi,
I am using the sample code and do not get the results I want. I am trying to combine all software records per HardwareName field. There is a seperate record for each software in the table for each hardwarename.
Example
Server 1 - Windows Server Standard 2003
Server 1 - Adobe 9
Server 1 - Policy Decisions
Server 2 - Windows 2008 Enterprise
Server 2 - DB2

I want all the software fields to combine into one textbox field on my report. The report is based on 'qryServerInfo'. A textbox(txtSoftware) on the report has the following for the control source:

=Concatenate("SELECT [Software] FROM [tblServerInfo] WHERE [HardwareName] = " & Reports!rptServerInfo!HardwareName)

The module codes is:
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 know it is probably an easy fix but cannot figure it out. Please help.
Lisa

 

[MajP]

My first guess
"...[HardwareName] = '" & Reports!rptServerInfo!HardwareName & "'")

 

[Eprice]

No that didn't work. I understand the change tho.
Lisa

 

[Eprice]

Yes, it did work after I got out and went back in. Do you know how to make each software a seperate line. I thought changing the delim to
Optional pstrDelim As String = vbCr) _
but it doesn't work.
Lisa

 

[Eprice]

I got that too. Thanks for you help.

 

[dhookom]

FYI, the optional delimiter can't be a vb constant. I expect Eprice used Chr(13) & Chr(10) as the delimiter.


Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

Hi Duane, thanks for the excellent code. I used the line
Optional pstrDelim As String = vbCrLf) _
As String

It works wonderful. Thanks again.
Lisa

 

[dhookom]

You solution will work. You also leave the function as is and send in the crlf:

"...[HardwareName] = '" & Reports!rptServerInfo!HardwareName & "'"[b][red], Chr(13) & Chr(10)[/red][/b])

Duane
Hook'D on Access
MS Access MVP