Summing and Counting a list of zero rows

Question

I have a table that often but not always has zero rows in it.

I want to display that table via a form, and in the footer I want to display the sum of numerical valued column from that table and a count of the number of records. These should be zero when there are no rows in the table./

The only way I've found to make it work, is to set Allow Additions to Yes, this appears to create a virtual dummy record and then the sum comes correctly to zero.

But I don't want to allow additions. I just want to display what is - or isn't on the table.

I've tired DSUM and DCOUNT but get no results when the table is empty. Nz doesn't help either it is not returning a null result just some undefined value.   

 Any help appreciated.. Derek V  

Answer 1

In a textbox Control Source:

=IIf (DCount("*") > 0, DCount("*") , 0)

Answer 2

Hi,

Can you show us how you used DCount()? I would think DCount() would have worked.

Answer 3

 ciao Derek,

for counting, unbound textbox with this rowsource : =count(*)

for the sum, unbound text box with this rowSource : nz(sum(myField);0)

get a look at the followig images ( my access version is in italian Language):

HTH, ciao, Sandro.

Answer 4

Hi DerekV1959,

is your issue solved?

I find that you did not follow up this thread after posting the issue.

if your issue is solved then I suggest you to post your solution and mark it as an answer.

if your issue is still exist then try to refer the solution given by the me.

if then also you have any further questions then let me know about it.

I will try to provide further suggestions to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi DerekV1959,

Could you share us a simple demo which could reproduce your issue?

I created a new table, generate a new form, disable Allow Additions, add a new textbox with datasource below, it works correctly.

=DCount("*","tblUser") 

tblUser is my table name.

Best Regards,

Tao Zhou

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].