none
total in subform : what is wrong ? RRS feed

  • 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

Answers

  • 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 tekoko10 Thursday, May 11, 2017 10:36 AM
    Thursday, May 11, 2017 9:33 AM
  • ciao Tekoko10,

    please, just Sandro thanks.

    HTH, Ciao, Sandro.

    • Marked as answer by tekoko10 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.

    what about this : 

    =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
  • Hi tekoko10,

    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
    Moderator
  • 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
  • Hi tekoko10,

    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
    Moderator
  • 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,

    what about this :

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

    instead than: 

    =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

    database

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

     it gives the error

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

    please, just Sandro thanks.

    HTH, Ciao, Sandro.

    • Marked as answer by tekoko10 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 tekoko10 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