Main Form and Sub Forms with calc control problem

[Sue]

I have a main form with two sub forms.

The main form is for an invoice entry. When an entry is
made to the main form - entries maybe made to one or both
of the subforms.

If an entry is not made to one of the sub forms, the
calculated control summing the order lines in that
subform is left blank (not even zero). This then affects
any calculation that involves this control.

How do I get it to put a zero in if no lines are added to
a subform, rather than leaving a blank?

Help greatfully received thankyou!

 

['69 Camaro]

Hi, Sue.

Use the Nz( ) function to replace the NULL with a numeric zero:

Nz(sumOfOrderLines, 0)

HTH,
Gunny

Coming soon:
For your Microsoft Access, database development and maintenance needs, see:
http://www.softomagixly.com

 

[Steve Schapel]

Sue,

As you have possibly discovered, the Nz() function is of no use in this
instance, as we are not dealing with a Null, we are dealing with
non-existent. I usually use a user-defined function for this type of
situation. In a standard module, put this...
Public Function nnz(testvalue As Variant) As Variant
' Code courtsey of Keri Hardwick
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Sub

Then, in your calculations that refer to the subform totals, use...
nnz([NameOfSubform]![NameOfTotalControl])