concatenation of multiple rows in queries Question!!

[mihalisp]

Hi everybody,

I have been using the following piece of code that refers to concatenation of multiple rows in queries.
http://www.access-programmers.co.uk/forums/showthread.php?t=64611

But i want to ask for something more complicated if anyone can help me in this forum.

I need a small adjustment of that piece of code but i am confused.

I want the concatenated field to include only distinct values

for example,when i concatenate multiple rows and the result is something like this: 1,1,2,1,3,3,4,1,3,2

i would prefer the rusult be 1,2,3,4

I tried to change the piece of code that refers to:

Do While Not rs.EOF
If Not IsNull(rs!Fld) Then

vFld = vFld & ", " & rs!Fld

End If
rs.MoveNext
Loop

but i dont know a lot of Functions and i got confused and couldnt make it work!

Full code is here:

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null

SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]='" & Value & "'"

' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly

' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then

vFld = vFld & ", " & rs!Fld

End If
rs.MoveNext
Loop

' remove leading comma and space.
vFld = Mid(vFld, 3)

Set cnn = Nothing
Set rs = Nothing

' return concatenated string.
Conc = vFld
End Function


I would be grateful if anyone could help me??

Thank you all in advance!!

 

[DCrake]

You would need to introduce a group by fieldx and sort by fieldx in your sql.

or before you concat the next record to your string chck for the existanc of the value in the string

if Instr(str,Value) = 0 Then

.....

end if

 

[mihalisp]

Thank you,

i will try the Instr function.

I think that maybe a "distinct" after the "select" would solve the problem!

What do you think about that?

 

[WayneRyan]

M,

A select Distinct would be perfect.

Also an Order By clause will help too.

Wayne

 

[mihalisp]

select distinct works!

I cant make Instr work!
I used the followin code but nothing still the same

Do While Not rs.EOF
If Not IsNull(rs!Fld) Then

If InStr("vFld", "rs!Fld") = 0 Then

vFld = vFld & ", " & rs!Fld
Else: vFld = vFld
End If

End If
rs.MoveNext
Loop


I dont care about the order of the row values,so i cant imagine how order by could do the job!

Thank you ALL!