#Error when Report returns no records

[Drew]

How can I make my ADP Report show a "No records" or 0 if there are no
records? I am using a SP and if the SP returns nothing then I get a #Error,
or if the report has been incorporated into another report as a subreport,
then the subreport does not show at all if there are no records.

Thanks,
Drew

 

[Drew]

I re-read my post and figured I didn't give enough information, here is some
more...

The SP is,

CREATE PROCEDURE spMetricsCollection_Supervisors
@EmpSex varchar(1),
@EmpRace char(1),
@StartDate datetime,
@EndDate datetime
AS
SELECT E.EmpID, CAT.Cat
FROM Courses C INNER JOIN Category CAT ON C.CatID = CAT.CatID INNER JOIN
ClassesTaught CT ON C.CourseTitle = CT.Title
INNER JOIN Training T ON CT.ClassNo = T.ClassNo INNER JOIN
EmpCore.dbo.tblEmployee E ON T.EmployeeNo = E.EmpID
INNER JOIN EmpPersonal.dbo.tblEmpPersonalInfo EP ON E.EmpID = EP.EmpID INNER
JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID
WHERE EP.EmpSex = @EmpSex AND
(CT.StartDate BETWEEN @StartDate AND @EndDate) AND (EndDate BETWEEN
@StartDate AND @EndDate) AND
EP.EmpRace = @EmpRace AND P.PosSupervisor = 1
ORDER BY EmpLName
GO

The report shows all CAT.Cat's returned and Count()'s the EmpIDs to find out
how many are in each category. This is where the screwy stuff is happening.
If the SP returns NO records, then I get an #Error for the Count([EmpID]),
if it returns records everything is fine. This really gets weird when I try
to use it as a subreport, then if there are NO records, then the subreport
is completely blank, no heading, no labels, nothing...

Anyone know what I can do to help out? I have tried, =IIf([EmpID] IS
NULL,0,Count([EmpID]), but that doesn't work at all... Does anyone have any
other suggestions?

Thanks,
Drew

 

[Allen Browne]

Simplest solution is to cancel the NoData event of the report, so it does
not show if there are no records.

The #Error occurs in calculated controls when there is no data. Basically
the calculation is referring to something that does not exist, and so
generates an error.

You can avoid that in calculated controls by testing the HasData property of
the report, e.g. setting the Control Source of the text box to:
=If([Report].[HasData], [Quantity] * [UnitPrice], Null)

Note that you also need to make sure the Name of the calculated text box is
not the same as the name of any field in the report's RecordSource. Access
gets confused if the control has the same Name as a field, but is bound to
something else.

The issue with subreports is the same: you get nothing at all if there are
no records to show. You could work around that by putting a text box on the
main report just above the subreport control names Sub1, and setting the
ContolSource of the text box to:
=IIf([Sub1].[Report].[HasData], Null, "Nuffin 2 show.")

HTH

 

[Guest]

Use the on no data event on the report to handle this issue, normally you'll
have to open a msgbox sayin that no records where found and then cancel the
execution of the report,

 

[Drew]

That worked the charm! I tried to post a follow-up message yesterday, but
the Internet went down before I could get it posted.

Thanks,
Drew

Simplest solution is to cancel the NoData event of the report, so it does
not show if there are no records.

The #Error occurs in calculated controls when there is no data. Basically
the calculation is referring to something that does not exist, and so
generates an error.

You can avoid that in calculated controls by testing the HasData property
of the report, e.g. setting the Control Source of the text box to:
=If([Report].[HasData], [Quantity] * [UnitPrice], Null)

Note that you also need to make sure the Name of the calculated text box
is not the same as the name of any field in the report's RecordSource.
Access gets confused if the control has the same Name as a field, but is
bound to something else.

The issue with subreports is the same: you get nothing at all if there are
no records to show. You could work around that by putting a text box on
the main report just above the subreport control names Sub1, and setting
the ContolSource of the text box to:
=IIf([Sub1].[Report].[HasData], Null, "Nuffin 2 show.")

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I make my ADP Report show a "No records" or 0 if there are no
records? I am using a SP and if the SP returns nothing then I get a
#Error, or if the report has been incorporated into another report as a
subreport, then the subreport does not show at all if there are no
records.

Thanks,
Drew