DCOUNT unique records

  • Thread starter Thread starter Rose B
  • Start date Start date
R

Rose B

I have a table (BefriendContact) which holds details of contacts made with
clients. Each client (ClientID) can have multiple contacts, the key of the
table is a contact ID. I want to be able to count the number of clients
contacted during a period defined by BeginningDate and EndingDate. The DCOUNT
in the report that I am using returns the number of contacts made, rather
than the number of clients contacted (which could be less if they were
contacted more than once duing the period).

=DCount("ClientID","BefriendContact","ContactDate >= #" &
[Forms]![frmReportByDateDialog]![BeginningDate] & "# and ContactDate <= #" &
[Forms]![frmReportByDateDialog]![EndingDate] & "#")

Can anyone suggest how to achieve what I am looking for? Do I need to create
a query to select the distinct lients?
 
In what context do you want to use this information? In a form? in a report?
in VBA code?

It's very difficult, if possible at all, to do what you seek using the
Domain functions (e.g., DCount, DLookup, etc.) based on your desire to
filter and then get distinct clients.
 
It was in a report - and apologies, I realise I shuld have posted in that
section. Since posting I have actually found an answer using "ECOUNT"
fundtion as described in http://allenbrowne.com/ser-66.html - this works
great!

Ken Snell (MVP) said:
In what context do you want to use this information? In a form? in a report?
in VBA code?

It's very difficult, if possible at all, to do what you seek using the
Domain functions (e.g., DCount, DLookup, etc.) based on your desire to
filter and then get distinct clients.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Rose B said:
I have a table (BefriendContact) which holds details of contacts made with
clients. Each client (ClientID) can have multiple contacts, the key of the
table is a contact ID. I want to be able to count the number of clients
contacted during a period defined by BeginningDate and EndingDate. The
DCOUNT
in the report that I am using returns the number of contacts made, rather
than the number of clients contacted (which could be less if they were
contacted more than once duing the period).

=DCount("ClientID","BefriendContact","ContactDate >= #" &
[Forms]![frmReportByDateDialog]![BeginningDate] & "# and ContactDate <= #"
&
[Forms]![frmReportByDateDialog]![EndingDate] & "#")

Can anyone suggest how to achieve what I am looking for? Do I need to
create
a query to select the distinct lients?
Click to expand...
Click to expand...
 
Back
Top