This should be trivial, sorry, just could not find it in google or help.
=Count(*) returns #Error instead of 0 when there are no records in the report (MS Access 97).
It seems I should test whether Count(*) returns an error state but cannot work out the syntax. I was expecting something like
=IIF(IsError(Count(*)),0,Count(*))
but Access does not recognise the Excel IsError() function. (No reason why it should be the same, of course).
Help? Thanks
Clint Westwood
Related resources
Replies (5)
Please login or register to join the discussion.
Thanks, that sort of works
although instead of a msgbox I would rather have a printable form that confirms no records, and the ideal output would be a report as designed and formatted, with the Count(*) textbox returning "0" instead of "#Error".
I would be surprised if Access provides no facility to test for an expression returning an error state. It would be virtually unique among programs of that calibre. Excel has the facility contained in =ISERROR(expression) and a whole family of similar functions such as ISERR() and ISNA().
In fact I would be so surprised at its omission from Access that I just cannot believe that it does not exist, and I feel sure that I simply have not found it. I note that they have gone to the trouble of providing an IsNull() function and NZ() function, both of which are similar to what I require but neither quite hitting the mark.
Solution would use code ...
Theory:
Use the 'NoData' event to trap then instead of killing the report write 0 to either the TextBox containing =Count(*) or hide it & write 0 to another normally hidden TextBox which is made visilbe on 'NoData'
Also if you want to put message to report try having invisible 'NO DATA' label message and enable it
Alernatively an invisible 'NO DATA' sub-form made visible etc.
Fair amount of messing about in code - but you do have error trapping in code.
From memory it's on error goto .... or on error resume (currently using .NET so it's a bit of rewind to remember Access). Even when was using Access refused to do most things via Wizards and used code (horrid doCmd commands) nearly all the time
Good as a samll db engine but a pain to develop anything more than simple apps
Use Report_NoData event .....
Not quite sure how you want to respond where no records are selected - however, assuming you wish to stop the report; nothing to report
You can keep
- your original select
- Control Source = Count(*)
Go into the code (view-->code) section behind the report (afraid I don't have a copy of '97 - tried on later version) and use the Report_NoData event as follows:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data Available"
Cancel = True
End Sub
Just copy above into the code section under Option Compare Database OR dropdown Combo containing 'General' & select Report then dropdown combo next to it (rhs) & select 'NoData'
Try this
Use a query - as follows ....
Create a query as follows - just copy the sql below & change table name to yours - then save the query as qryTestCount:
SELECT Count(*) AS RecordCount FROM TestRecordCount
(you can run the query separately to ensure it works ok)
Then - in report-->properties
Click - RecordSource and choose the query saved above (qryTestCount)
Now create a textBox and bring up its properties - under Control Source (property) dropdown and select RecordCount (the only field available)
Then run the report
Basically you are using a query as the source for the report; this provides a great deal of flexibility and in other circumstances allows selection criteria and/or sort order
Thanks for the suggestion, but ...
... I have two problems with it.
The first (and trivial) problem is that it would require me to generate an additional query for each affected report (there are dozens of them), which clutters up the query list a bit (can live with that).
The second is that I simply cannot get it to work. I can create, save and run the query, and it returns the correct count, as well as zero rather than error when there are no records. So far so good. My problem is getting it into the report.
If I change the RecordSource in the report then I lose all of the other items included in the report that were derived from the query table being counted.
I tried creating a new report using the wizard, and right-arrowed the relevant fields from the original query table AND the RecordCount query table, but when I clicked on "next" it bombs out with the following error message:
"You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."
Well, I was certainly doing what it suggested was the cause of the problem. And computer he say "not permitted".