Errors with nulls in reports - please help

[jarekz]

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!

[puppydogbuddy]

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.

[jarekz]

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!

[puppydogbuddy]

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.

[jarekz]

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?

[puppydogbuddy]

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.

[jarekz]

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! :-)