DCount / Count(if) within Report?

[andmunn]

Hello,

I'm trying to count the number of "issues" produced in a report (the issue's are sorted by categories). The same issue can possibly appear twice in the report, if it applies to several different categories. I want the function to, however, only count the issue once.

Right now this code:

=Count(IIf(([IssueRisk]="High") And [IssueSource]="Computer" And ([CurrentStatus]="In Progress"),0))

returns the correct count - but will duplicate count the same issue if it appears in two categories. How can i only count distinct values?

I tried coding this:

=DCount("[IssueID]","2012 Report","[pkPersonID]=Forms!frmReporting!ChoosePerson AND [IssueRisk] = " & Chr(34) & "HIGH" & Chr(34))

It works - however, i can't see how i can add the other critiria (i..e// issue soure, and current status)

Any help is aprpeciated.

Thanks!
Andrew.

 

[vbaInet]

You basically need to perform the count based on a query that displays distinct IssueRisks. You will need DCount for this.

Or you can run code in the Format event of the Detail section.

 

[andmunn]

Thanks Vba..

That went right over my head - can you expand a little bit? Perhaps using the DCount method you referenced below...

Andrew.

 

[vbaInet]

DCount cannot count distinct values unless the table or query from which it is performing the operation is returning distinct values.

http://office.microsoft.com/en-us/a...t-distinctrow-top-predicates-HA001231351.aspx

The domain (i.e. data source) of the DCount function will then be the query (above) that returns distinct IssueRisks values.