Query return 0 values affecting Report

Resolved · Urgent Priority · Version 2016

Sun 11 Oct 2020, 18:27 Delegate Manny said...

Manny has attended:
Access Intermediate course
Access Advanced course

Query return 0 values affecting Report

Hello,

I have built a report based on an underlying query.
The query has many joins and "many to one" relationships.

The query also has 2 separate queries that are joined using a 1-2-1 relationship. Both of these queries search separate tables to find values. These queries return 0 records (correctly!), but then causes the entire report output to be blank. This query searches for bank transactions. If therefore it cannot find any records, there are no bank transactions (essentially a 0).

How can i control for the 2 separate queries to return 0 value (it would be a 0 value as it can't find any records)?

Mon 12 Oct 2020, 13:54 Trainer Jens said...

RE: Query return 0 values affecting Report

Hi Manny,

Thank you for the forum question.

I am not sure I understand what you want to achieve, but if you want a zero in your report if the query has no records, you can add a text box to the report and type:

=IIf(Count(*) Is Null,0,Count(*))

This will tell you how many records there is in the underlying query.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector