title: MS Access How to Count Unique Records or Values On a Filtered Report
body: <p>All,</p>
  
  <p>I have a query called "WCR_Q" and a report called "WCR_R" based off of this query.  My report is filtered based off a record selection from a form.  When I use the expression =Count[FacilityID] becuse I get a total number of records in the query instead of the total number of records as displayed in my report.  The query has (6) records for [FacilityID] but there's only (2) [FacilityID] in my report.  This is due to the query having duplicates.  I need an expression formula to place in my control that will count only unique records in the query or <em>only count the records expressed on the report</em>.  </p>
  
  <p>I've tried:   </p>
  
  <pre><code> =Count([FacilityID])
   =DCount("[FacilityID]","WCR_Q",DCount("[FacilityID]","WCR_Q","FacilityID =" &amp; [FacilityId])&gt;0)
   =DCount("[FacilityID]","WCR_Q","FacilityID =" &amp; [FacilityID])
   =DCount("[FacilityID","[WCR_Q]","[FacilityID]=" &amp; [FacilityID])&gt;0
   =Count(IIf("FacilityID &lt;&gt;" &amp; [FacilityID],0))
   =DCount("[FacilityID]","WCR_Q",[FacilityID])
   =DCount("[FacilityID]","WCR_Q","FacilityID &lt;&gt;" &amp; [FacilityID])
  </code></pre>
  
  <p>I have tried the count functions in the Facility grouping and the Campaign grouping and it returns a value of (6) instead of (2) expressed in report.  </p>
  
  <p>I'm new to writing expressions and designing reports in Access so please help if anyone knows how to count unique values in a control box.  Thanks!!!</p>