Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Test for no-value in control

Posted on 2001-06-08
Medium Priority
200 Views
Last Modified: 2011-09-20
I have a report with 2 sub-reports. The report is a customer statement between 2 dates showing his/her orders and payments.

One sub-report shows all orders between the 2 dates, and the other sub-report shows all payments between the 2 dates. On each sub-report there is a non-visible control which is the sum of orders (or payments).

This non-visible control is used to supply the total orders and payments values to controls on the main report.
The controls on the main report have control source as:
=[sbrOrders].Report![txtSumOrders] and
=[sbrPayments].Report![txtSumPayments].

[sbrOrders] and [sbrPayments] are the names of the 2 sub-reports. [txtSumOrders] and [txtSumPayments] are the non-visible controls.

A problem occurs when there are no orders (or payments) between the 2 dates. There is no value in the total control on the main report, not even Null.

How do I test for this situation so that I can run alternative code?

Thanks.
0
Question by:Annu
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
5 Comments

Expert Comment

ID: 6170481
You may use this:
=iif(isnull([sbrOrders].Report![txtSumOrders])=true;0;=[sbrOrders].Report![txtSumOrders])
or like this
=iif(isnumeric([sbrOrders].Report![txtSumOrders])=true;0;=[sbrOrders].Report![txtSumOrders])
0

Expert Comment

ID: 6170483
Opps..
=iif(isnull([sbrOrders].Report![txtSumOrders])=true;0;[sbrOrders].Report![txtSumOrders])
and
=iif(isnumeric([sbrOrders].Report![txtSumOrders])=true;0;[sbrOrders].Report![txtSumOrders])
0

LVL 5

Expert Comment

ID: 6171783
Or use something like

If nz(sbrOrders.Value) > 0 Then
Process
Else
Nothing there
End if
0

LVL 6

Expert Comment

ID: 6172941
Have you tried the 'On No Data' evnt in the Report?
0

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 6173260
The problem is that a subform or subreport with no records returns an error, not a null.  So you can either use IsError with an IIf check to return a zero:

=IIf(IsError([sbrOrders].Report![txtSumOrders]),0,[sbrOrders].Report![txtSumOrders])

or you can use the function below like so:

=AvoidError([sbrOrders].Report![txtSumOrders])

Jim.

Function AvoidError(n As Variant)

On Error GoTo Trap
AvoidError = n
Exit Function

Trap:
AvoidError = 0
Resume Next

End Function
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll

#### 721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.