Count Function/ #Error (1 Viewer)

KatCanDo · Nov 29, 2006

I need help with a Count function. =Count works fine unless there is no data in the report then I get #Error. I understand why this is happening but I can not correctly format the function to count the data but IIf IsNull then count as a zero and return total number of items in this field.

This control is in the report footer and the report is based on a query which gives me a list of items that do not match on a table (compares two fields on the table which should be exactly alike).

Eg:

# of Discrepancies Shown =Count ([fieldname])

boblarson · Nov 29, 2006

Code:

# of Discrepancies Shown =Count (Nz([fieldname],0))

KatCanDo · Nov 29, 2006

Same thing happening

I am still getting the #Error on the reports with no data. Is there any other information that I may be leaving out to solve this problem?

boblarson · Nov 29, 2006

In the report, you have to make sure that the fieldname does not also have a control with the same name. So, for example, you can't have a field name of CustomerName and then have the text box that displays it in the report as CustomerName (which is what the default is thanks to Microsoft's wizard).

KatCanDo · Dec 1, 2006

Well,

I played around with this a bit with these results. Changing the control name had no effect, if I put in the Nz part in without =Count the results returned are blank instead of #Error.

I would rather have a zero "0" in the field instead of blank. My ultimate goal is a summary report which shows the # of Discrepancies Shown for multiple detail reports in a summary report. I had thought I could design this summary report by referring back to the discrepancy total in each detail report.

Thanks for your help!

boblarson · Dec 1, 2006

Another thing to check for is that when you rename the control, Access will, if Autocorrect is enabled, update the formula too. So, reopen the formula and make sure it doesn't have the control name, but has the field name. When doing formulas in reports, you have to refer to the FIELD name and NOT the CONTROL name.

KatCanDo · Dec 5, 2006

Ok, none of this has worked so far

I have been reading the forum and trying various things but still have the #Error message occuring on my reports that have no records to report.

Sorry to be a pain but if you guys have any other suggestions I really would appreciate the help

boblarson · Dec 5, 2006

If you can compact your database, zip it (so it is under 393kb) and upload it here, maybe we can spot the problem. I'm more visual and it helps me to be able to play with it until I can get the actual fix. Just a suggestion.

KatCanDo · Dec 5, 2006

I wish I could

but it's too big and contains sensitive information.

DB design is several tables each with many fields. Eg: Age, DOB, Address, etc.

Most fields contain data to compare before event and after event. Other fields are identifying information, SS# etc.

Queries pull information to show what data no longer matches after event.
Eg: BeforeAge<>AfterAge

Reports show details of queries with a footer that Counts how many records showing up as bad (not matching after event).

Footer on report works fine and returns correct Count as long as there are records to count otherwise I get #Error.

And finally, I need a summary report with the the Count from each report on all one final report. Can not figure this out either as the information I need is in the footer.

And I thought I was learning Access really well for a while there

)

Brianwarnock · Dec 5, 2006

Apologies if this is off target but maybe you need to do
=Sum(IIf([fieldname] is null,0,1))

Brian

KatCanDo · Dec 5, 2006

=Sum(IIf([fieldname] IsNull,0,1))

"The expression you entered contains invalid syntax. You may have entered an operand without an operator."

I have looked at this many times and called over a co-worker, neither of us can see what is different in what I typed in and what you suggested.

Brianwarnock · Dec 5, 2006

I think is null is 2 words here, I know it isn't in other places.

brian

boblarson · Dec 5, 2006

It should be:

Code:

=Sum(IIf(IsNull([fieldname]),0,1))

Brianwarnock · Dec 5, 2006

I think they both work, the Is Null approach was in my mind as I had recently corrected an Is Not Null example.

brian

KatCanDo · Dec 5, 2006

Ah, well,

=Sum(IIf(IsNull([fieldname]),0,1))

does not give the syntax error I was getting previously, but I still get the #Error.

boblarson · Dec 5, 2006

The only thing I can think of is that you're fieldname is named the same as a control on your report. The control that is bound to the fieldname cannot have the same name as the fieldname when using that fieldname in any kind of aggregate function or formula.

So, if my field I am wanting a sum for is named "DollarsSpent" and I used the Microsoft Report Wizard, or drag and dropped the field name from the field list, then the textbox that shows "DollarsSpent" would have the same name as the field. I would then need to change the text box to something like "txtDollarsSpent" so that it had a different name.

Now, if I had already typed in "=Sum(IIf(IsNull([DollarsSpent]),0,1))" then Access will be so kind to change [DollarsSpent] to [txtDollarsSpent] even though I don't want it to (thanks to Autocorrect). So, I would need to go back and change it back to "=Sum(IIf(IsNull([DollarsSpent]),0,1))" so that it would work.

Rich · Dec 6, 2006

You cannot count nothing. There is a difference between records with null values and no records at all.

Brianwarnock · Dec 6, 2006

Rich said:
You cannot count nothing. There is a difference between records with null values and no records at all.

Whilst that is a very valid point in a query it would not produce an error , but a blank result, wouldn't the same happen in the report?

Brian

Rich · Dec 6, 2006

If there are no records then one either has to use the NoData event of the Report or the HasData method if it's a subReport

KatCanDo · Dec 6, 2006

Still working on this

and sometime back I put a msg box on no data event to pop up that says "No records to report" to try and make this look a bit more professional.

As you say the query produces no data for the report because in this case the two columns being compared are the same. So there are no mistakes to report.

Count Function/ #Error (1 Viewer)

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Retired

Registered User.

Retired

Smeghead

Retired

Registered User.

Smeghead

Rich

Guest

Retired

Rich

Guest

Registered User.

Similar threads

Users who are viewing this thread