Using report.subreport.hasdata with multiple IIf's

Robbie Doo 65 Reputation points
2026-08-31T15:30:03.36+00:00

When I'm using (subreport.hasdata.report) feature on main form to pull data with one IIf option, it works fine. However, when I add another IIf, I get and error.

For example:

=IIf([rpt01_TestCount].[Report].[HasData] And [PU_Loc]=1 And [Trvl_Type]=1,[rpt01_TestCount].[Report].[Text0],0)

Any help on this is appreciated.

Microsoft 365 and Office | Access | For business | Windows
0 comments
Answer accepted by question author
Senthil kumar 2,410 Reputation points
2026-08-31T15:40:51.9266667+00:00

Hi @Robbie Doo

Access sometimes still evaluates the inner expression too early. This version forces Access to short‑circuit:

=IIf([rpt01_TestCount].[Report].[HasData]=True And [PU_Loc]=1 And [Trvl_Type]=1,
        [rpt01_TestCount].[Report].[Text0],
        0)

But if this still errors, use the nested version above — it is the most stable.

Use nested iif 

=IIf([rpt01_TestCount].[Report].[HasData],
        IIf([PU_Loc]=1 And [Trvl_Type]=1,
            [rpt01_TestCount].[Report].[Text0],
            0),
        0)

Access evaluates expressions left to right. With the nested version:

If the subreport has no data, Access returns 0 immediately

It never tries to evaluate [Text0] or the other conditions

  • No error

Thanks.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.