Using DCount to count # of records containing matching form field 2

[tgharris]

I am trying to use DCount() to find out how many records in a related table "Estimates" there are based on the current value of the ProjectID field [long] in a form (Me![ProjectID]). I have tried looking up DCount() in MS Help, but I can;t seem to format the arguments properly. Thank you in advance.

 

[DougP]

Try this on for size<br>
In a query or report<br>
=DCount(Forms!Orders![ShipCountry])<br>
or VBA code<br>
x=DCount(Forms!Orders![ShipCountry])<br>

 

[rhicks]

Try this, the IIf statement will handle null entries:<br>
<br>
=IIf([ProjectID] Is Null,Null,DCount(&quot;ProjectID&quot;,&quot;Estimates&quot;,&quot;([ProjectID] = &quot; & [Forms]![yourFormName]![ProjectID] & &quot&quot)<br>
<br>
Note: The code should all be on one line.<br>
<br>
HTH<br>
RDH<br>
<br>

 

[tgharris]

DougP, rhicks, thanks a lot for your help.<br>
<br>
What finally worked was<br>
int ProjectID=Me![ProjectID]<br>
intCount=DCount([ProjectID], &quot;Estimates&quot;, [ProjectID]=intProjectID)<br>
where [ProjectID] is a field in the form and the table &quot;Estimates&quot;.<br>