Concatenate Query

[Dean Fraiquin]

Duane,

Here is the SQL as it stands:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service], Concatenate([«pstrSQL»],[«pstrDelim»])
AS Expr1
FROM Services;

All help is appreciated!

Regards,

Dean

-----Original Message-----
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP

Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean

-----Original Message-----
You can use a generic function. Copy the below code

into
a new module and

save it as "basConcatenate". Then, you can use it in a query as noted in the
code.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'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
MS Access MVP


Hi,

I have a database with Company info, and a Products table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the web, and
it seems possible but I do not really understand the SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my dreams
come true???

Many thanks for any help.

Dean Fraiquin


.

.

..