# total in subform : what is wrong ?

• ### Question

• I have a field in a subform

=(IIf(IsNull([SomVandozen]);120;120-[SomVandozen]))

I want to make the total of it , but it gives #error

I tried also

=val(IIf(IsNull([SomVandozen]);120;120-[SomVandozen])))

but it gives the same error

if I add this total to the footer of that subform , all totals in the subform get the same #error

what am I doing wrong  ?

Wednesday, May 10, 2017 9:42 AM

• ciao Tekoko10,

in your scenario stocktot is the controlname of unbound control.

infact, if you change controlName to "Pippo" or something else sum works anytime.

sum function needs to be applied to bound control and not to unbound on controlName.

if you got classic textBox in which you show =qty*untiPrice and its name is txtAmount

to show total amount you have to invoke =sum(qty*untiPrice) and not =sum(txtAmount)

HTH.

Sandro.

• Marked as answer by Thursday, May 11, 2017 10:36 AM
Thursday, May 11, 2017 9:33 AM
• ciao Tekoko10,

HTH, Ciao, Sandro.

• Marked as answer by Thursday, May 11, 2017 8:35 AM
Thursday, May 11, 2017 8:03 AM

### All replies

• =(IIf(IsNull([SomVandozen]);120;120-[SomVandozen]))

Hi tekoko,

Perhaps the expression is too complex for totalling.

You could try a simpler expression:

`=(120-Nz(SomVandozen,0))`

Imb.

Wednesday, May 10, 2017 9:58 AM
• ciao Tekoko10,

have you got some other control in which some other formula is wrong?

Apart one more parenthesis close it seems correct.

=120-Nz([SomVandozen];0)

ciao, Sandro.

Wednesday, May 10, 2017 10:00 AM
• =(IIf(IsNull([SomVandozen]);120;120-[SomVandozen]))

Try replacing the semicolons with commas.

Build a little, test a little

Wednesday, May 10, 2017 5:26 PM
• I try to test your line code on my side.

modified line of code:

```Private Sub Command13_Click()
Me.Text11.Value = val(IIf(IsNull([Text9]), 120, 120 - [Text9]))
End Sub```

you need to remove one extra bracket in the last and use commas instead of semi colon.

Regards

Deepak

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, May 11, 2017 3:22 AM
• strange , it is still the same .

I have 2 totals in the subform , they are fine ,

I make a field with the third total of fields with =(120-Nz(SomVandozen,0)) and than all the totals give #error ...

Thursday, May 11, 2017 6:20 AM
• I try to test that line.

```Private Sub Command13_Click()
Me.Text11.Value = (120 - Nz(Text9, 0))
End Sub```

it is working fine on my side.

please try to delete that field and try to create a new field and try to test the code again.

it looks like something get corrupted.

if that not work then try to "Compact and Repair" your database.

if possible then try to post whole code. so that we can try to test it on our side.

also post which error occurs.

Regards

Deepak

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Thursday, May 11, 2017 6:27 AM
• the compact and repair did not help ...

I 've put a database with the form here

https://1drv.ms/u/s!AgFM2MmvS5-CgheA6-zCcK_MEr7V

perhaps someone can have a look :

if you remove the total field in the middle , the other totals work...

Thursday, May 11, 2017 7:05 AM
• ciao Tekoko10,

=Somma(120-Nz([SomVandozen];0))

=Somma([stocktot])

ciao, Sandro.

Thursday, May 11, 2017 7:39 AM
• Mr Peruz,

gives the same error

the orange field causes errors for the other totals   (som  = SUM)

if you remove that field , the other totals are fine

here is the database

https://1drv.ms/u/s!AgFM2MmvS5-CgheA6-zCcK_MEr7V

it gives the error

﻿﻿
Thursday, May 11, 2017 7:51 AM
• ciao Tekoko10,

HTH, Ciao, Sandro.

• Marked as answer by Thursday, May 11, 2017 8:35 AM
Thursday, May 11, 2017 8:03 AM
• Sandro , this works , why ?
Thursday, May 11, 2017 8:35 AM
• ciao Tekoko10,

in your scenario stocktot is the controlname of unbound control.

infact, if you change controlName to "Pippo" or something else sum works anytime.

sum function needs to be applied to bound control and not to unbound on controlName.

if you got classic textBox in which you show =qty*untiPrice and its name is txtAmount

to show total amount you have to invoke =sum(qty*untiPrice) and not =sum(txtAmount)

HTH.

Sandro.

• Marked as answer by Thursday, May 11, 2017 10:36 AM
Thursday, May 11, 2017 9:33 AM
• didn't know that , many thanks Sandro
Thursday, May 11, 2017 10:37 AM