Calculate totals from nested forms

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • oncewaswillow@hotmail.com

    #1

    Calculate totals from nested forms

    Hi,
    I have 3 nested forms and want to calculate a total on the first
    subform (as the form loads)by adding 2 fields from that form
    [deposit]+[rental] to a field on the second subform.
    Second subform is [transactional subform] and control is Sumpd.
    I use this

    =[transactiontbl subform].Form!Sumpd+[deppd]+[rentpd]

    which works well as long as the transactiontbl subform has records in
    it. Problem is this subform will often be empty. I then get error as a
    result in my calculated control. Have tried using Iif statements eg

    =IIf(Me![transactiontbl
    subform].Form.RecordCou nt=0,[deppd]+[rentpd],[transactiontbl
    subform].Form!Sumpd+[deppd]+[rentpd])

    but as a newbie am surely writing something wrong!
    All forms are nested with master/child fields linked. Table
    relationships also look OK.
    Any help much appreciated.

  • Phil Stanton

    #2
    Re: Calculate totals from nested forms

    Try - Nz([transactiontbl subform].Form!Sumpd) + Nz(deppd) + Nz(rentpd)
    I'm not sure it will work, but give it a go. Nz is used to convert Nulls to
    Zeros.
    Incidentally you don't need square brackets [] round field name or table
    names PROVIDING there are no spaces in them. Makes expressions a little
    simpler, but suggest you never use spaces in field name e.g. use
    TransactionTblS ubform

    HTH

    Phil

    <oncewaswillow@ hotmail.com> wrote in message
    news:1113141409 .142472.89040@g 14g2000cwa.goog legroups.com...[color=blue]
    > Hi,
    > I have 3 nested forms and want to calculate a total on the first
    > subform (as the form loads)by adding 2 fields from that form
    > [deposit]+[rental] to a field on the second subform.
    > Second subform is [transactional subform] and control is Sumpd.
    > I use this
    >
    > =[transactiontbl subform].Form!Sumpd+[deppd]+[rentpd]
    >
    > which works well as long as the transactiontbl subform has records in
    > it. Problem is this subform will often be empty. I then get error as a
    > result in my calculated control. Have tried using Iif statements eg
    >
    > =IIf(Me![transactiontbl
    > subform].Form.RecordCou nt=0,[deppd]+[rentpd],[transactiontbl
    > subform].Form!Sumpd+[deppd]+[rentpd])
    >
    > but as a newbie am surely writing something wrong!
    > All forms are nested with master/child fields linked. Table
    > relationships also look OK.
    > Any help much appreciated.
    >[/color]


    Comment

    Working...