Dcount from table

[theinviter]

Dears;
I have a table" Listing_of_documents_of_Purchas"
I created a query named "

so I need to add a column in the query to get the number of record with status "pending" . and Voucher1 match each other .

i tried below but not working. as take long time to run.
Pend: Nz(DCount("[Status]","[Listing_of_documents_of_Purchas]","[Voucher1] ='" & [Voucher1] & "' AND [Status] = '" & "Pending" & "'"),0)

so any help please .

 

[CJ_London]

you don't need NZ because dcount can never return null

have you tried using an aggregate query?

Not enough info provided to provided to suggest what that might look like since 'and Voucher1 match each other ' does not make sense or at least, has no context. But perhaps might look something like

SELECT Voucher1, Count(*) as Pend
FROM Listing_of_documents_of_Purchas
WHERE Status='Pending'
GROUP BY Voucher1

 

[arnelgp]

Pend: DCount("1","[Listing_of_documents_of_Purchas]","[Voucher1] ='" & [Voucher1] & "' AND [Status] ='Pending'")

 

[Pat Hartman]

NEVER use domain functions in a query unless using a different method would make the query not updateable. Each domain function runs a separate query. So, if your recordset is 1000 records, you are running an additional 1000 queries when a join will always be more efficient.

CJ's suggestion is the preferred method when you want just ONE value.

This query will give you a list of all status counts by voucher.
SELECT Voucher1, Status, Count(*) as StatusCount
FROM Listing_of_documents_of_Purchas
GROUP BY Voucher1