How do I remove duplicates in a report concatenated field? 1

[jzabielski]

Purpose is to generate a report showing each Facility on a single line with the total Cost of only the Funded projects and a block of the Keywords associated with only the Funded projects. The Keyword block should only contain a single occurrence of each Keyword.

Each Facility has multiple Projects. Each Project record contains a Keyword, Funded and Cost field.

The report is fed from a query that correctly totals only Funded projects and is grouped to display a single Keyword with the total for all projects associated with that Keyword.

Currently the report correctly shows a single Facility with the correct total Cost of only the Funded projects and a concatenation of the Keywords. The problem is that concatenation contains all Keywords for both funded and unfunded projects including multiple occurrences of the Keywords.

The report unbound concatenation container is named AllKeywords.

The On Format of the Sectionetail contains the following code:

Option Compare Database
Option Explicit
Dim FirstPass As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Local Error GoTo Detail_Format_Err

If Not FirstPass Then
Me!AllKeywords = Me![strKeyWord]
FirstPass = True
Else
Me!AllKeywords = Me!AllKeywords & ", " & Me![strKeyWord]
End If

Detail_Format_End:
Exit Sub

Detail_Format_Err:
MsgBox Error$
Resume Detail_Format_End

End Sub

This is a split database using 2000 on the (server) back end and 2003 on the front end.

Thanks in advance.

 

[Remou]

How about:

[tt]If instr(Me!AllKeywords,Me![strKeyWord])=0 Then
Me!AllKeywords = Me!AllKeywords & ", " & Me![strKeyWord]
End If[/tt]

 

[jzabielski]

Brilliant!

That accomplished exactly what it needed to. It even had the additional benefit of identifying Projects missing a Keyword. The concatenation displayed nothing for that Project making it extremely easy to identify.

Thanks again.