How to Find Distinct Records by Using DCount?

[JithuAccess]

Hello Guys,

How can I use Distinct Function with DCount?

This is my Code

=DCount("[strEmp ID]","[tblMonthly Report Raw Data]","[datPayment Date] Between #2020-04-01# and #2020-04-30#")

I want to find the Unique Employee ID. there are Total 10 Employee IDs and 2 of them are Repeated and in my Report I want to show Just 8 Not 10.

Could you please let me know how to Fix this?

Thanks

 

[Isaac]

I think you'd have to write something for that, dcount cannot

 

[theDBguy]

You could create a DISTINCT query and DCount() that.

 

[JithuAccess]

You could create a DISTINCT query and DCount() that.

Can we give something like [StrEmp ID]=Unique in the Criteria?

Thanks

 

[theDBguy]

Can we give something like [StrEmp ID]=Unique in the Criteria?

Thanks

No, not with the built-in DCount(). You could, as @Isaac said, create your own function that will allow you to do that.

Or, you could use this one:

Microsoft Access tips: Extended DCount()

Code to count the number of records in a table/query in Microsoft Access, with the option to count the number of distinct values. A replacement for DCount().

allenbrowne.com

 

[JithuAccess]

No, not with the built-in DCount(). You could, as @Isaac said, create your own function that will allow you to do that.

Or, you could use this one:

Microsoft Access tips: Extended DCount()

Code to count the number of records in a table/query in Microsoft Access, with the option to count the number of distinct values. A replacement for DCount().

allenbrowne.com

Thank You. I will go with Query

 

[moke123]

You can also hide duplicates in access reports.

MS Access Report Design Tip -Access Database Tutorial

MS Access Report Design Tip: How To Hide Duplicates (Selectively) In Your Access Reports ? There are many approaches to using logic in controls......

accessdatabasetutorial.com