Help with DCount

[den4673]

Hello, I am hoping someone can help me and tell me how to correct my
problem.
My report is based on an Invoice query, where each invoice has a date,
amount and corresponding week number. In the report in the detail
footer, I want a summary by week number for Total Invoice Amount and
count of number of Invoices with zero amount. I can get the invoice
total amount, howerver, the count of invoices with zero amount returns
the same amount for each week number which is the overall report total
for each week rather than the weekly count.

Below is an example of what I have done so far which is in the detail
footer section:

=DCount("InvNum ","[qryInvCount]","[Sumofsvcamt]=0'")

This returns all of the invoices with zero amount rather than just the
ones for the week.

Any suggestions are appreciated.

Dennis

[Rick Brandt]

de*****@yahoo.c om wrote:

Hello, I am hoping someone can help me and tell me how to correct my
problem.
My report is based on an Invoice query, where each invoice has a date,
amount and corresponding week number. In the report in the detail
footer, I want a summary by week number for Total Invoice Amount and
count of number of Invoices with zero amount. I can get the invoice
total amount, howerver, the count of invoices with zero amount returns
the same amount for each week number which is the overall report total
for each week rather than the weekly count.

Below is an example of what I have done so far which is in the detail
footer section:

=DCount("InvNum ","[qryInvCount]","[Sumofsvcamt]=0'")

This returns all of the invoices with zero amount rather than just the
ones for the week.

Any suggestions are appreciated.

Dennis

First tell us what section this is really in. The detail section has no footer
nor header.

The Domain aggregate functions return the same value regardless of where you use
them unless you include a field from the report's query in the WHERE argument.
In your case you would need to include the week number field in the WHERE clause
if you want the number per-week.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[den4673]

On Aug 12, 8:57 am, "Rick Brandt" <rickbran...@ho tmail.comwrote:

den4...@yahoo.c om wrote:

Hello, I am hoping someone can help me and tell me how to correct my
problem.
My report is based on an Invoice query, where each invoice has a date,
amount and corresponding week number. In the report in the detail
footer, I want a summary by week number for Total Invoice Amount and
count of number of Invoices with zero amount. I can get the invoice
total amount, howerver, the count of invoices with zero amount returns
the same amount for each week number which is the overall report total
for each week rather than the weekly count.

Below is an example of what I have done so far which is in the detail
footer section:

=DCount("InvNum ","[qryInvCount]","[Sumofsvcamt]=0'")

This returns all of the invoices with zero amount rather than just the
ones for the week.

Any suggestions are appreciated.

Dennis

First tell us what section this is really in. The detail section has no footer
nor header.

The Domain aggregate functions return the same value regardless of where you use
them unless you include a field from the report's query in the WHERE argument.
In your case you would need to include the week number field in the WHERE clause
if you want the number per-week.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thanks for the response and the explanation. I will give it a try.

I have a group header for the week number and I have the detail
summarized in the group footer, which I am concerned with now. It is
the group footer that I erroneously referred to as detail footer.

Dennis

[Rick Brandt]

de*****@yahoo.c om wrote:

Thanks for the response and the explanation. I will give it a try.

I have a group header for the week number and I have the detail
summarized in the group footer, which I am concerned with now. It is
the group footer that I erroneously referred to as detail footer.

There is likely a solution that doesn't require a domain function at all (which
are best avoided in reports and queries). Try this in the week number footer...

=Sum(IIf([amount]=0, 1, 0))

That should return the number of invoices in the week number group that have a
zero amount.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[fredg]

On Sun, 12 Aug 2007 05:28:05 -0700, de*****@yahoo.c om wrote:

Hello, I am hoping someone can help me and tell me how to correct my
problem.
My report is based on an Invoice query, where each invoice has a date,
amount and corresponding week number. In the report in the detail
footer, I want a summary by week number for Total Invoice Amount and
count of number of Invoices with zero amount. I can get the invoice
total amount, howerver, the count of invoices with zero amount returns
the same amount for each week number which is the overall report total
for each week rather than the weekly count.

Below is an example of what I have done so far which is in the detail
footer section:

=DCount("InvNum ","[qryInvCount]","[Sumofsvcamt]=0'")

This returns all of the invoices with zero amount rather than just the
ones for the week.

Any suggestions are appreciated.

Dennis

Well, I see an extraneous ' in your code. "[Sumofsvcamt]=0'")
Try:
=DCount("*","[qryInvCount]","[Sumofsvcamt]=0")

The above assumes [Sumofsvcamt] actually contains the value of 0, and
is not simply Null.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[den4673]

On Aug 12, 9:50 am, "Rick Brandt" <rickbran...@ho tmail.comwrote:

den4...@yahoo.c om wrote:

Thanks for the response and the explanation. I will give it a try.

I have a group header for the week number and I have the detail
summarized in the group footer, which I am concerned with now. It is
the group footer that I erroneously referred to as detail footer.

There is likely a solution that doesn't require a domain function at all (which
are best avoided in reports and queries). Try this in the week number footer...

=Sum(IIf([amount]=0, 1, 0))

That should return the number of invoices in the week number group that have a
zero amount.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Thank you so much, that was the answer.

Thanks for the help,

Dennis