How can I nest expressions correctly ? 1

[testkitt2]

I have a small form that has a few textboxes in the detail part that are controlled by a query. At the form "forms footer" I have a textbox that adds all the textboxes in the detail. The only thing is that if the a textbox is currently blank the textbox in the "form footer" will also be blank because it can not add... I guess numbers with a textbox that is blank.
How can I nest the next textbox into this expression.

below is an expression that if the textbox is null to give it a value of zero, this way the columns will add up.
The way this expression is setup it works... the only thing is that I need to nest another textbox to this expression so that It can also give it a value of zero and add up.
below is my expression, and each [...]is the name of each textbox.

=Sum(IIf(IsNull([ContDARTow]),(0),[ContDARTow])+([Sum Of ContParts]+[Sum Of ContLubes]+[Sum Of Cont3rdParty]+[Sum Of ContTires]+[Sum Of ContOthers]))

Thank you always
JZ


Testkitt2

 

[CautionMP]

Two thoughts[ol][li]Make 0 (zero) the default field value in the underlying datasource, this will make the formula straight forward addition until a user deletes the zero[/li][li]Write a small function to do the math:

Private Function FormTotal() As Double
   FormTotal = Nz([ContDarTow]) + Nz([Sum Of ContParts]) + Nz([Sum Of ContLubes]) + Nz([Sum Of Cont3rdParty]) + Nz([Sum Of ContTires]) + Nz([Sum Of ContOthers])
End Function

the [tt]Nz()[/tt] will convert any null values to zero for you. Paste this code in the forms' module and replace your [tt]=sum(...)[/tt] above with [tt]=FormTotal[/tt][/li][/ol]
Hope this helps,
CMP

Instant programmer, just add coffee.

 

[testkitt2]

Hey thanks CautionMP

I tried to enter =FormTotal then tried =FormTotal() and someothers but returned #name error.
So this is whats in the place of =Sum... see below

and it works great...surely the =formtotal could be used in serveral places with a little tweaking.

but hey thanks a lot....

=Sum(Nz([ContDARTow])+Nz([Sum Of ContParts])+Nz([Sum Of ContLubes])+Nz([Sum Of Cont3rdParty])+Nz([Sum Of ContTires])+Nz([Sum Of ContOthers]))

JZ

Testkitt2

 

[testkitt2]

Thanks again CautionMP
I copied and pasted this code exactly in the the textbox control source and it works but when I tried to use it again in another textbox on the same form with a little changing of the textnames it returned a blank box in both textboxes the original one with the code and the second one I added the code to.....Why? it that.

=Sum(Nz([ContDARTow])+Nz([Sum Of ContParts])+Nz([Sum Of ContLubes])+Nz([Sum Of Cont3rdParty])+Nz([Sum Of ContTires])+Nz([Sum Of ContOthers]))

Thanks
JZ

Testkitt2

 

[CautionMP]

...with a little changing of the textnames...

Did you change the field name or control name that is part of the formula?

I did a little prototyping to try and re-create what you are experiencing, no dice unless I mis-typed a field name or control name when building the formula.

A quick note: When I used the [tt]=Sum(...)[/tt] I got totals from the entire underlying recordset. If I renamed all the text boxes (in practice I never leave a control with the same name as the underlying field) I was able to use [tt]=Nz([Text0])+Nz([Text1])+Nz([Text2])[/tt] to just get a total from the text boxes on the form.

CMP



Instant programmer, just add coffee.

 

[testkitt2]

Hi CautionMP
No I did not change the name of the text boxes...what I meant was that I change the name to reflect the name of the textboxes I was already using to calc the sum. But, when I used the Nz([...... is returned a blank all the way around..and what I mean is that although the first total of textboxes worked fine.... by itself...it also made them blank...///confused...
thanks
JZ

Testkitt2