Nz function

[reb0101]

hey guys,
I'm hoping someone here can help me with that elusive 'Nz" function.
Here's what I am trying to do.
I have a main form, called LIN_NOMEN.
There are 3 subforms in LIN_NOMEN:
29SUB, 31SUB and 41SUB.
each one of these subs has a total box that calculates a total.
When the user selects a LIN from LIN_NOMEN, it will link to that LIN on the sub forms and display that
record and the totals for the record.
So far so good.
Here's the hitch:
There may be a matching LIN in 1, 2 or all 3 of the subforms.
I am trying to calculate the total of the totals of the subform(s) on the main LIN_NOMEN form.
I have a text box and this is the code I have for it:

[29SUB].Form![TOTALS] + [33SUB].Form![TOTALS] + [41SUB].Form![TOTALS]

Now, if ALL 3 SUBS has a value for LIN, then it displays the correct total.
But If only one of the subs or even 2 out of three has a matching LIN (and a total in it's total column)
there it does not add them and display a total at all.
I have used the Nz function that will essentially ignore a null or "0" but it's been cut and paste and cross my fingers.
Not working now.
Can anyone tell me where to plug in the Nz and get it to work here?
Thanks for any and all help.

 

[pbaldy]

Try

http://access.mvps.org/access/forms/frm0022.htm

 

[reb0101]

thanks, but this really does not help much nor have the last couple of hours on Nz searches.
I have created three text boxes on my main form and pulled the totals values from all the subforms
29SUB, 33SUB and 41SUB to get the values on the main form without all of the levels going into the total
text on the main form.
I'll then do the additions all on the main form.
But the problem, as expected, persists.
This VBA module is great but I'm sorry, VBA is not one of my strengths by a long shot and even if I cut and paste this and create the module I have no idea how to 'attach' it to either the main form or the subs for it to do it's magic and convert to the Nz where needed.
I have been at this for so long I have to take a break and get back to it later

 

[pbaldy]

It's cut and paste. You use it like

nnz(29SUB.Form!Totals)

 

[Pat Hartman]

Nz([29SUB].Form![TOTALS],0) + Nz([33SUB].Form![TOTALS],0) + Nz([41SUB].Form![TOTALS],0)

As you can see, the Nz() MUST enclose EACH element that might be null. Ie. you have to convert EACH control to a numeric value indepenently. If you do Nz(a + b + c, 0) you will get a total if ALL values are non- null but 0 when ANY one of them is null.

 

[ChrisO]

Reb0101.

The problem is not:-
[29SUB].Form![TOTALS] + [33SUB].Form![TOTALS] + [41SUB].Form![TOTALS]

The problem is:-
[29SUB].Form![TOTALS]

If any one of the three can cause the failure then it is the one which needs to be looked at, not all three at once.

It is a common mistake to try and solve the entire problem without first trying to solve each individual part of the problem.

Chris.

 

[pbaldy]

As you can see, the Nz() MUST enclose EACH element that might be null.

But the Nz() function will not work in this situation.

 

[Pat Hartman]

I don't experience the error you linked to on a form where I reference totals in the subform's footer. If that is happening for the OP, then the code needs to be:

IIf(IsError([29SUB].Form![TOTALS]),0,Nz([29SUB].Form![TOTALS],0)) +
IIf(IsError([33SUB].Form![TOTALS]),0,Nz([33SUB].Form![TOTALS],0)) +
IIf(IsError([41SUB].Form![TOTALS]),0,Nz([41SUB].Form![TOTALS],0))

 

[ChrisO]

At the moment I do not think there is sufficient data to come to any firm conclusion.

If we take one subform and try to return the value to its Parent:-

Private Sub cmdGetTotal_Click()

    MsgBox IIf(IsError([29SUB].Form![Totals]), 0, Nz([29SUB].Form![Totals], 0))

End Sub

Then the above code will fail if there are no records in the subform AND, on the subform, Allow Additions is set to No.
(Under that circumstance there are no Controls visible on the subform.)


But if we allow the subform to handle its own errors we can use this on the Parent Form:-

Private Sub cmdGetTotal_Click()

    MsgBox Me.[29SUB].Form.GetTotal()

End Sub

Where the Form in the subform control [29SUB] has:-

Public Function GetTotal() As Double

    On Error Resume Next
    
    GetTotal = Me.Totals
    
    If (Err.Number) Then
        Err.Clear
        GetTotal = 0
    End If

End Function

In essence then:
We require each Form to report back zero (0) from its own GetTotal() function even if an error is raised in that particular Form.

Each Form can then be tested individually. That is what I meant by… “It is a common mistake to try and solve the entire problem without first trying to solve each individual part of the problem.”

Once we have each Form reporting its own correct value we can sum the values of those Forms in the Parent Form; for example:-

Private Sub cmdGetTotal_Click()

    MsgBox Me.[29SUB].Form.GetTotal() + Me.[33SUB].Form.GetTotal() + Me.[41SUB].Form.GetTotal()

End Sub

Chris.

 

[reb0101]

hey everyone,
sorry for the delay in getting back and thanks to all of you for your input.
The VB worked, took a little tweaking and learned some in the process but saved me more hours of frustration.
Thanks again to all.