Help with Dcount Function

[khwaja]

I am using the following function in a report which can be triggered from a form for the associasted record.

I can't seem to get it right. I am trying to count number of records in tbDV matching project ID but I also would like to apply a condition that this count should include only records where in tbDV, field DVStatus field has 'approved'.

Will appreciate some help.

=DCount("[projectid]","tbDV","[ProjectID] = " & [Reports]![rptProjectRecord].[projectid] And "[DVStatus]"="Approved")

 

[mrrcomp]

HI

In your criteria you need to add a space between the first and second values and you need to add quotes around the text string

DCount("[projectid]","tbDV","[ProjectID] = " & [Reports]![rptProjectRecord].[projectid] And " [DVStatus]=" & chr(34) & "Approved" & chr(34))

The above should work..

Regards

 

[pbaldy]

The problem is you didn't restart the string after the report reference (and some quote issues). Try

=DCount("[projectid]","tbDV","[ProjectID] = " & [Reports]![rptProjectRecord].[projectid] & " And [DVStatus] ='Approved'")

 

[khwaja]

I am very grateful for your help.

 

[pbaldy]

Happy to help.

 

[shieriel]

Hello. Can i post my question here? It is related with the inquiry above..
I also want to count the number of records per product and i want to show on my report only those product having 30data and more.
When i try to input product name on my form and select "show report" it should only show all the data if the sum of its data is 30 and above but if its less than 30 it shows empty. On my actual report it also shows the data even if the particular product has less than 30data...
This sounds complicated to me and i can't find a solution as of this time..hope you can help

 

[pbaldy]

It sounds like you should be using a totals query, with a HAVING clause. Something along the lines of:

SELECT Product, Count(*) As HowMany
FROM TableName
GROUP BY Product
HAVING Count(*) >= 30