Count records, but not dupes

[Chrissy]

I am designing a report to return new clients, by source, by client-since
date, within a date range. I have a form I use to enter dates that calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In those
headers, I count invoices, dollars and average them. This is as expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients [ClientID] for
those same groups-no dupes, display and use results in calculations. Note
that each new client in the month they are new, may have multiple invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls within the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I correct?

I appreciate any help.

Thanks.

 

[Allen Browne]

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask for a
distinct count, so you can use a text box with Control Source like this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

 

[Chrissy]

Thanks, Allen. That seems a great tool. I am still having a bit of issue,
though. It returns the TOTAL count of clients for the report's range.

I cannot figure how to make it count only within a group, returning only the
count for that group. When I place a control with ECode into each group it
returns all clients for that date. I thought it would return only that
group's clients. I tried to adjust the code, but I don't know how to ensure
selected group count.

How do I code it to only count of clients in the group it is placed in?

I have now: =ECount("ClientID","[tbl 1 Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask for a
distinct count, so you can use a text box with Control Source like this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by client-since
date, within a date range. I have a form I use to enter dates that calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In those
headers, I count invoices, dollars and average them. This is as expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients [ClientID] for
those same groups-no dupes, display and use results in calculations. Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I
correct?

I appreciate any help.

Thanks.

 

[Allen Browne]

I'm not sure exactly how your report is laid out, but if a group is defined
as a particular client and a particular date, you could put that into the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's range.

I cannot figure how to make it count only within a group, returning only
the
count for that group. When I place a control with ECode into each group
it
returns all clients for that date. I thought it would return only that
group's clients. I tried to adjust the code, but I don't know how to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed in?

I have now: =ECount("ClientID","[tbl 1 Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask
for a
distinct count, so you can use a text box with Control Source like this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients [ClientID]
for
those same groups-no dupes, display and use results in calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I
correct?

I appreciate any help.

Thanks.

 

[Chrissy]

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to $x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the way?


Again, thank you so much,
--
Chrissy

I'm not sure exactly how your report is laid out, but if a group is defined
as a particular client and a particular date, you could put that into the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's range.

I cannot figure how to make it count only within a group, returning only
the
count for that group. When I place a control with ECode into each group
it
returns all clients for that date. I thought it would return only that
group's clients. I tried to adjust the code, but I don't know how to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed in?

I have now: =ECount("ClientID","[tbl 1 Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask
for a
distinct count, so you can use a text box with Control Source like this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients [ClientID]
for
those same groups-no dupes, display and use results in calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I
correct?

I appreciate any help.

Thanks.

 

[Allen Browne]

As you can imagine, it's hard for us to see exactly what you are doing, as
we cannot see your report, its sections, or its source query and the tables
that feed that.

Presumably you have some text boxes on a form that provides the limiting
dates. If so, you can concatenate the value of the dates into the Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level, another
alternative is to use a subquery in the source for the report, so it brings
the totals into the report. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to $x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the
way?


Again, thank you so much,
--
Chrissy

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that into the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's range.

I cannot figure how to make it count only within a group, returning
only
the
count for that group. When I place a control with ECode into each
group
it
returns all clients for that date. I thought it would return only that
group's clients. I tried to adjust the code, but I don't know how to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask
for a
distinct count, so you can use a text box with Control Source like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In
those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I
correct?

I appreciate any help.

Thanks.

 

[Chrissy]

Yes, I know it must be difficult and I do appreciate the aid.

I have tried using subquery and could not get it to work out for me so I
revisited your ECount. I just cannot understand why that won't do it for me.


In a text box in the highest level group I have entered

=ECount("[ClientID]","[tbl 1 Client]","([SinceDate] Between
[Forms]![9frmDateSelector]![txtStartDate] And
[Forms]![9frmDateSelector]![txtEndDate]) And [Source]=[txtSource]",True)

and get "ECount error 3061: Too few parameters -- expected 3".

Is my code in error? All I want is to count unique clients for the Source
between those report dates.

Again, I am very thankful for your guidance.

--
Chrissy

As you can imagine, it's hard for us to see exactly what you are doing, as
we cannot see your report, its sections, or its source query and the tables
that feed that.

Presumably you have some text boxes on a form that provides the limiting
dates. If so, you can concatenate the value of the dates into the Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level, another
alternative is to use a subquery in the source for the report, so it brings
the totals into the report. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to $x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the
way?


Again, thank you so much,
--
Chrissy

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that into the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's range.

I cannot figure how to make it count only within a group, returning
only
the
count for that group. When I place a control with ECode into each
group
it
returns all clients for that date. I thought it would return only that
group's clients. I tried to adjust the code, but I don't know how to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask
for a
distinct count, so you can use a text box with Control Source like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In
those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I
correct?

I appreciate any help.

Thanks.

 

[Chrissy]

Addendum to the earlier reply...

I achieved the correct results when I was specific as follows:

=ECount("[ClientID]","[tbl 1 Client]","[Source]='Google' and [SinceDate]
between #12/1/08# and #12/31/08#",True)


I was unsuccessful when I tried:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=[txtSource] and [SinceDate]
between [txtStartDate]and [txtEndDate]",True)


I guess it is an issue with [txtSource] and [txtStartDate]/[txtEndDate]. I
also guess it is a timing issue as those text boxes print/view data on the
report as expected. So.....I must not be able to use them or they are empty
when ECount looks.

Is this a clue?

--
Chrissy

As you can imagine, it's hard for us to see exactly what you are doing, as
we cannot see your report, its sections, or its source query and the tables
that feed that.

Presumably you have some text boxes on a form that provides the limiting
dates. If so, you can concatenate the value of the dates into the Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level, another
alternative is to use a subquery in the source for the report, so it brings
the totals into the report. If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to $x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the
way?


Again, thank you so much,
--
Chrissy

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that into the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's range.

I cannot figure how to make it count only within a group, returning
only
the
count for that group. When I place a control with ECode into each
group
it
returns all clients for that date. I thought it would return only that
group's clients. I tried to adjust the code, but I don't know how to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you ask
for a
distinct count, so you can use a text box with Control Source like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In
those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for the
[SinceDate] group (monthly) when placed in that group header -- am I
correct?

I appreciate any help.

Thanks.

 

[Allen Browne]

It can't understand values that are inside the string, such as:
"[Source]=[txtSource]"

You need to concatenate the values into the string, e.g.:
"[Source]=" & [txtSource]

If Source is a Text field, you need extra quotes:
"[Source]=""" & [txtSource] & """"
Explained here:
http://allenbrowne.com/casu-17.html

If Source is a Date field, you need # as the delimiter:
"[Source]=#" & [txtSource] & "#"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Addendum to the earlier reply...

I achieved the correct results when I was specific as follows:

=ECount("[ClientID]","[tbl 1 Client]","[Source]='Google' and [SinceDate]
between #12/1/08# and #12/31/08#",True)


I was unsuccessful when I tried:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=[txtSource] and
[SinceDate]
between [txtStartDate]and [txtEndDate]",True)


I guess it is an issue with [txtSource] and [txtStartDate]/[txtEndDate].
I
also guess it is a timing issue as those text boxes print/view data on the
report as expected. So.....I must not be able to use them or they are
empty
when ECount looks.

Is this a clue?

--
Chrissy

As you can imagine, it's hard for us to see exactly what you are doing,
as
we cannot see your report, its sections, or its source query and the
tables
that feed that.

Presumably you have some text boxes on a form that provides the limiting
dates. If so, you can concatenate the value of the dates into the Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level,
another
alternative is to use a subquery in the source for the report, so it
brings
the totals into the report. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and
the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to
$x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the
way?


Again, thank you so much,
--
Chrissy


:

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that into
the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's
range.

I cannot figure how to make it count only within a group, returning
only
the
count for that group. When I place a control with ECode into each
group
it
returns all clients for that date. I thought it would return only
that
group's clients. I tried to adjust the code, but I don't know how
to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed
in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you
ask
for a
distinct count, so you can use a text box with Control Source like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates
that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In
those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in
calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I
need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for
the
[SinceDate] group (monthly) when placed in that group header --
am I
correct?

 

[Chrissy]

Thanks -- again.

I read the page, it makes sense to me except for dates when using Between/And.

I modified my control source for simplicity, avoiding dates for the moment.
I get a return of #Name with the following:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Can you tell me why? In this case it isn't obvious to me.


Thanks,

--
Chrissy

It can't understand values that are inside the string, such as:
"[Source]=[txtSource]"

You need to concatenate the values into the string, e.g.:
"[Source]=" & [txtSource]

If Source is a Text field, you need extra quotes:
"[Source]=""" & [txtSource] & """"
Explained here:
http://allenbrowne.com/casu-17.html

If Source is a Date field, you need # as the delimiter:
"[Source]=#" & [txtSource] & "#"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Addendum to the earlier reply...

I achieved the correct results when I was specific as follows:

=ECount("[ClientID]","[tbl 1 Client]","[Source]='Google' and [SinceDate]
between #12/1/08# and #12/31/08#",True)


I was unsuccessful when I tried:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=[txtSource] and
[SinceDate]
between [txtStartDate]and [txtEndDate]",True)


I guess it is an issue with [txtSource] and [txtStartDate]/[txtEndDate].
I
also guess it is a timing issue as those text boxes print/view data on the
report as expected. So.....I must not be able to use them or they are
empty
when ECount looks.

Is this a clue?

--
Chrissy

As you can imagine, it's hard for us to see exactly what you are doing,
as
we cannot see your report, its sections, or its source query and the
tables
that feed that.

Presumably you have some text boxes on a form that provides the limiting
dates. If so, you can concatenate the value of the dates into the Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level,
another
alternative is to use a subquery in the source for the report, so it
brings
the totals into the report. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices and
the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to
$x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me the
way?


Again, thank you so much,
--
Chrissy


:

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that into
the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit of
issue,
though. It returns the TOTAL count of clients for the report's
range.

I cannot figure how to make it count only within a group, returning
only
the
count for that group. When I place a control with ECode into each
group
it
returns all clients for that date. I thought it would return only
that
group's clients. I tried to adjust the code, but I don't know how
to
ensure
selected group count.

How do I code it to only count of clients in the group it is placed
in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets you
ask
for a
distinct count, so you can use a text box with Control Source like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter dates
that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month). In
those
headers, I count invoices, dollars and average them. This is as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in
calculations.
Note
that each new client in the month they are new, may have multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I
need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate] falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also for
the
[SinceDate] group (monthly) when placed in that group header --
am I
correct?

 

[Allen Browne]

Make sure that:

a) The Name property of this text box is not the same as any fields in the
form (e.g. it must not be named Source.) Access gets confused if a control
has the same name as a field but it bound to something else.

b) There is a text box named Source on the form where you use this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks -- again.

I read the page, it makes sense to me except for dates when using
Between/And.

I modified my control source for simplicity, avoiding dates for the
moment.
I get a return of #Name with the following:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Can you tell me why? In this case it isn't obvious to me.


Thanks,

--
Chrissy

It can't understand values that are inside the string, such as:
"[Source]=[txtSource]"

You need to concatenate the values into the string, e.g.:
"[Source]=" & [txtSource]

If Source is a Text field, you need extra quotes:
"[Source]=""" & [txtSource] & """"
Explained here:
http://allenbrowne.com/casu-17.html

If Source is a Date field, you need # as the delimiter:
"[Source]=#" & [txtSource] & "#"

Addendum to the earlier reply...

I achieved the correct results when I was specific as follows:

=ECount("[ClientID]","[tbl 1 Client]","[Source]='Google' and
[SinceDate]
between #12/1/08# and #12/31/08#",True)


I was unsuccessful when I tried:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=[txtSource] and
[SinceDate]
between [txtStartDate]and [txtEndDate]",True)


I guess it is an issue with [txtSource] and
[txtStartDate]/[txtEndDate].
I
also guess it is a timing issue as those text boxes print/view data on
the
report as expected. So.....I must not be able to use them or they are
empty
when ECount looks.

Is this a clue?

--
Chrissy


:

As you can imagine, it's hard for us to see exactly what you are
doing,
as
we cannot see your report, its sections, or its source query and the
tables
that feed that.

Presumably you have some text boxes on a form that provides the
limiting
dates. If so, you can concatenate the value of the dates into the
Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level,
another
alternative is to use a subquery in the source for the report, so it
brings
the totals into the report. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices
and
the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to
$x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me
the
way?


Again, thank you so much,
--
Chrissy


:

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that
into
the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit
of
issue,
though. It returns the TOTAL count of clients for the report's
range.

I cannot figure how to make it count only within a group,
returning
only
the
count for that group. When I place a control with ECode into
each
group
it
returns all clients for that date. I thought it would return
only
that
group's clients. I tried to adjust the code, but I don't know
how
to
ensure
selected group count.

How do I code it to only count of clients in the group it is
placed
in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets
you
ask
for a
distinct count, so you can use a text box with Control Source
like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter
dates
that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month).
In
those
headers, I count invoices, dollars and average them. This is
as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in
calculations.
Note
that each new client in the month they are new, may have
multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice
count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I
need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate]
falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also
for
the
[SinceDate] group (monthly) when placed in that group
header --
am I
correct?

 

[Chrissy]

GroupHeader0 is grouped on [Source].

No other text box is named or control sourced to "source".

A text box is named [txtSource], control source [Source] and in GroupHeader0.

These variations as control source of the text box in question, both return
#Name.
=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [txtSource] & """")
=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Thanks.
--
Chrissy

Make sure that:

a) The Name property of this text box is not the same as any fields in the
form (e.g. it must not be named Source.) Access gets confused if a control
has the same name as a field but it bound to something else.

b) There is a text box named Source on the form where you use this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks -- again.

I read the page, it makes sense to me except for dates when using
Between/And.

I modified my control source for simplicity, avoiding dates for the
moment.
I get a return of #Name with the following:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Can you tell me why? In this case it isn't obvious to me.


Thanks,

--
Chrissy

It can't understand values that are inside the string, such as:
"[Source]=[txtSource]"

You need to concatenate the values into the string, e.g.:
"[Source]=" & [txtSource]

If Source is a Text field, you need extra quotes:
"[Source]=""" & [txtSource] & """"
Explained here:
http://allenbrowne.com/casu-17.html

If Source is a Date field, you need # as the delimiter:
"[Source]=#" & [txtSource] & "#"

Addendum to the earlier reply...

I achieved the correct results when I was specific as follows:

=ECount("[ClientID]","[tbl 1 Client]","[Source]='Google' and
[SinceDate]
between #12/1/08# and #12/31/08#",True)


I was unsuccessful when I tried:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=[txtSource] and
[SinceDate]
between [txtStartDate]and [txtEndDate]",True)


I guess it is an issue with [txtSource] and
[txtStartDate]/[txtEndDate].
I
also guess it is a timing issue as those text boxes print/view data on
the
report as expected. So.....I must not be able to use them or they are
empty
when ECount looks.

Is this a clue?

--
Chrissy


:

As you can imagine, it's hard for us to see exactly what you are
doing,
as
we cannot see your report, its sections, or its source query and the
tables
that feed that.

Presumably you have some text boxes on a form that provides the
limiting
dates. If so, you can concatenate the value of the dates into the
Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level,
another
alternative is to use a subquery in the source for the report, so it
brings
the totals into the report. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices
and
the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to
$x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me
the
way?


Again, thank you so much,
--
Chrissy


:

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that
into
the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit
of
issue,
though. It returns the TOTAL count of clients for the report's
range.

I cannot figure how to make it count only within a group,
returning
only
the
count for that group. When I place a control with ECode into
each
group
it
returns all clients for that date. I thought it would return
only
that
group's clients. I tried to adjust the code, but I don't know
how
to
ensure
selected group count.

How do I code it to only count of clients in the group it is
placed
in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets
you
ask
for a
distinct count, so you can use a text box with Control Source
like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter
dates
that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month).
In
those
headers, I count invoices, dollars and average them. This is
as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in
calculations.
Note
that each new client in the month they are new, may have
multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice
count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I
need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate]
falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also
for
the
[SinceDate] group (monthly) when placed in that group
header --
am I
correct?

 

[Chrissy]

I may be close...I copied the report and removed the groups, got ECount to
return good numbers, then re-grouped [Source].

It works! But now I must qualify it with the date range. Hmmm...how do I
concatenate the date and the source? Do you have a page for dates?

Can you show me how?

Thanks.
--
Chrissy

Make sure that:

a) The Name property of this text box is not the same as any fields in the
form (e.g. it must not be named Source.) Access gets confused if a control
has the same name as a field but it bound to something else.

b) There is a text box named Source on the form where you use this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks -- again.

I read the page, it makes sense to me except for dates when using
Between/And.

I modified my control source for simplicity, avoiding dates for the
moment.
I get a return of #Name with the following:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Can you tell me why? In this case it isn't obvious to me.


Thanks,

--
Chrissy

It can't understand values that are inside the string, such as:
"[Source]=[txtSource]"

You need to concatenate the values into the string, e.g.:
"[Source]=" & [txtSource]

If Source is a Text field, you need extra quotes:
"[Source]=""" & [txtSource] & """"
Explained here:
http://allenbrowne.com/casu-17.html

If Source is a Date field, you need # as the delimiter:
"[Source]=#" & [txtSource] & "#"

Addendum to the earlier reply...

I achieved the correct results when I was specific as follows:

=ECount("[ClientID]","[tbl 1 Client]","[Source]='Google' and
[SinceDate]
between #12/1/08# and #12/31/08#",True)


I was unsuccessful when I tried:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=[txtSource] and
[SinceDate]
between [txtStartDate]and [txtEndDate]",True)


I guess it is an issue with [txtSource] and
[txtStartDate]/[txtEndDate].
I
also guess it is a timing issue as those text boxes print/view data on
the
report as expected. So.....I must not be able to use them or they are
empty
when ECount looks.

Is this a clue?

--
Chrissy


:

As you can imagine, it's hard for us to see exactly what you are
doing,
as
we cannot see your report, its sections, or its source query and the
tables
that feed that.

Presumably you have some text boxes on a form that provides the
limiting
dates. If so, you can concatenate the value of the dates into the
Control
Source of the text box on the report.

If you can't figure out the criteria to use at the text-box level,
another
alternative is to use a subquery in the source for the report, so it
brings
the totals into the report. If subqueries are new, here's an
introduction:
http://allenbrowne.com/subquery-01.html

I must have misled you.

I group on [Source]...this is how we found the client.

For this group I have text boxes that return the count of invoices
and
the
sum of $. These report correctly.

What I need is to count the number of unique clients for this group.

For example, I may have 20 clients that have 25 invoices that sum to
$x.

I currently have the 25 invoice count and the $x sum.

I need the 20 client count. Did I mislead, if so, can you show me
the
way?


Again, thank you so much,
--
Chrissy


:

I'm not sure exactly how your report is laid out, but if a group is
defined
as a particular client and a particular date, you could put that
into
the
criteria for the function, e.g.:

=ECount("ClientID", "[tbl 1 Client]",
"(ClientID = " & [ClientID] &
") AND (SaleDate = " & Format([SaleDate], "\#mm\/dd\/yyyy\#") &
")", True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. That seems a great tool. I am still having a bit
of
issue,
though. It returns the TOTAL count of clients for the report's
range.

I cannot figure how to make it count only within a group,
returning
only
the
count for that group. When I place a control with ECode into
each
group
it
returns all clients for that date. I thought it would return
only
that
group's clients. I tried to adjust the code, but I don't know
how
to
ensure
selected group count.

How do I code it to only count of clients in the group it is
placed
in?

I have now: =ECount("ClientID","[tbl 1
Client]",[Report].[Filter],True)
and my groups are [Source] and [SinceDate].

I am strictly an amateur.

Thanks for the help.
--
Chrissy


:

Copy the ECount() function from this web page:
http://allenbrowne.com/ser-66.html

This is an extended DCount(), with an extra argument that lets
you
ask
for a
distinct count, so you can use a text box with Control Source
like
this:
=ECount("ClientID", "Table1", [Report].[Filter], True)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am designing a report to return new clients, by source, by
client-since
date, within a date range. I have a form I use to enter
dates
that
calls
the report, passing the dates.

I have groups on [Source] and [SinceDate] (grouped by month).
In
those
headers, I count invoices, dollars and average them. This is
as
expected,
groups SinceDates for each Source. The numbers are correct.

My issue is that I want to count the number of new clients
[ClientID]
for
those same groups-no dupes, display and use results in
calculations.
Note
that each new client in the month they are new, may have
multiple
invoices.
Thus, when I count [ClientID] it always equals the invoice
count.

I have searched and tried Count, DCount, tried counting on the
report's
record source, to no avail.

How do I count, in those groups, the new clients only once? I
need
something like:

=Count(NewClientsNoDupes for this group, where [SinceDate]
falls
within
the
date range I am passing to the report)

What is placed in the [Source] group header should work also
for
the
[SinceDate] group (monthly) when placed in that group
header --
am I
correct?

 

[Allen Browne]

Try adding a hidden text box named Source, bound to the Source field. That
just might solve the #Name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

GroupHeader0 is grouped on [Source].

No other text box is named or control sourced to "source".

A text box is named [txtSource], control source [Source] and in
GroupHeader0.

These variations as control source of the text box in question, both
return
#Name.
=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [txtSource] & """")
=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Thanks.
--
Chrissy

Make sure that:

a) The Name property of this text box is not the same as any fields in
the
form (e.g. it must not be named Source.) Access gets confused if a
control
has the same name as a field but it bound to something else.

b) There is a text box named Source on the form where you use this.

Thanks -- again.

I read the page, it makes sense to me except for dates when using
Between/And.

I modified my control source for simplicity, avoiding dates for the
moment.
I get a return of #Name with the following:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Can you tell me why? In this case it isn't obvious to me.

 

[Allen Browne]

It works! But now I must qualify it with the date range. Hmmm...how do I
concatenate the date and the source? Do you have a page for dates?

Can you show me how?

See the example in my 2nd reply to your original q.

 

[Chrissy]

Did you see my second reply about success after regrouping?
--
Chrissy

Try adding a hidden text box named Source, bound to the Source field. That
just might solve the #Name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

GroupHeader0 is grouped on [Source].

No other text box is named or control sourced to "source".

A text box is named [txtSource], control source [Source] and in
GroupHeader0.

These variations as control source of the text box in question, both
return
#Name.
=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [txtSource] & """")
=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Thanks.
--
Chrissy

Make sure that:

a) The Name property of this text box is not the same as any fields in
the
form (e.g. it must not be named Source.) Access gets confused if a
control
has the same name as a field but it bound to something else.

b) There is a text box named Source on the form where you use this.

Thanks -- again.

I read the page, it makes sense to me except for dates when using
Between/And.

I modified my control source for simplicity, avoiding dates for the
moment.
I get a return of #Name with the following:

=ECount("[ClientID]","[tbl 1 Client]","[Source]=""" & [Source] & """")

Can you tell me why? In this case it isn't obvious to me.