By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

Errors with nulls in reports - please help

P: 13
Hi guys,

I'm not a newbie when it comes to MS Access, but I can't sort it out. Can you help me?

My report is based on a query that returns 0, 1, 2,... 10 or more records. The report displays all records (they are called batches) and below it displays their number (e.g. 5 records in total). I count the number of returned records using a field that value is "=COUNT (BATCH)". Everything works fine when there are 1, 2 or more batches. But when there is no batches the report displays "#Error" (or sth like that) instead of 0". How can COUNT function give an error instead of zero? What do I do wrong? I tried to experiment with Nz, but no luck. Anybody knows what I should do? Thank you for any help!
Dec 26 '07 #1
Share this Question
Share on Google+
6 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hi guys,

I'm not a newbie when it comes to MS Access, but I can't sort it out. Can you help me?

My report is based on a query that returns 0, 1, 2,... 10 or more records. The report displays all records (they are called batches) and below it displays their number (e.g. 5 records in total). I count the number of returned records using a field that value is "=COUNT (BATCH)". Everything works fine when there are 1, 2 or more batches. But when there is no batches the report displays "#Error" (or sth like that) instead of 0". How can COUNT function give an error instead of zero? What do I do wrong? I tried to experiment with Nz, but no luck. Anybody knows what I should do? Thank you for any help!
Try = Count(*)
This should give you a count of the records returned from your query, irrespective of whether or not a batch # has been assigned.
Dec 28 '07 #2

P: 13
Try = Count(*)
This should give you a count of the records returned from your query, irrespective of whether or not a batch # has been assigned.
Thank you for your reply, but it still doesn't work.
I tried "=Count(*)" and I tried "=IIf(IsNull(Count(*)),0,Count(*))" and I tried "=Nz(Count(*), 0)" and it still gives me "#Error" when Batch Number is not assigned. I can't understand what the problem is!
Dec 28 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Thank you for your reply, but it still doesn't work.
I tried "=Count(*)" and I tried "=IIf(IsNull(Count(*)),0,Count(*))" and I tried "=Nz(Count(*), 0)" and it still gives me "#Error" when Batch Number is not assigned. I can't understand what the problem is!
The only thing that I can think of is that the textbox where you are invoking the count function has a non-numeric format. Invoke the property sheet for the textbox check the format property. make sure that the format is numeric.
Dec 28 '07 #4

P: 13
The only thing that I can think of is that the textbox where you are invoking the count function has a non-numeric format. Invoke the property sheet for the textbox check the format property. make sure that the format is numeric.
Thanks for your input again, puppydogbuddy. I have to admit that it didn't come to my mind. That textbox was created automatically by Report Wizard (or whatever it is called in MS Access), so I have no idea what format it is. I don't have MS Access at home, but I will check it as soon as I come back to work (which will happen on January 2).

But if the format is non-numeric, why does it work for numbers greater than zero?
Dec 29 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
Thanks for your input again, puppydogbuddy. I have to admit that it didn't come to my mind. That textbox was created automatically by Report Wizard (or whatever it is called in MS Access), so I have no idea what format it is. I don't have MS Access at home, but I will check it as soon as I come back to work (which will happen on January 2).

But if the format is non-numeric, why does it work for numbers greater than zero?
Oh, I did not know you were not getting #Error where Count was > 0. Then, there has to be something wrong with your nz syntax....your sample expressions were all enclosed in quotes...was that just for this writeup or are you using quotes in the textbox??? If you are, that is probably your problem.
Dec 30 '07 #6

P: 13
Oh, I did not know you were not getting #Error where Count was > 0. Then, there has to be something wrong with your nz syntax....your sample expressions were all enclosed in quotes...was that just for this writeup or are you using quotes in the textbox??? If you are, that is probably your problem.
I'm not using quotes in the textbox, only in my posts.
As soon as I come back to work I will take a look at the text box and report properties, maybe something is hidden there and I didn't notice that?
What destroys me is that I waste my time for something so simple (but it's not the first time in MS Access).

Thanks for your help and Happy New Year! :-)
Dec 30 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.