Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90

    Unanswered: Get total of three fields on query

    I'm using this expression in my query

    Payment Total: Nz([AmountPaid1]+[AmountPaid2]+[AmountPaid3];0)

    but it's only returning results where each of the three 'amount paid' fields have values in them, not, for instance, when [AmountPaid1] has a value but [AmountPaid2] and [AmountPaid3] doesn't.

    I'm sure this is a quick fix for somebody. Many thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the nz function allows you to supply a value if the resultant expression is null
    so you should put an NZ expression around each of the columns


    Nz([AmountPaid1],0)+Nz([AmountPaid2],0)+Nz([AmountPaid3],0)



    ... a suggestion when you hit a problem take a step back and try to analyse what is happening as opposed to what you think should happen
    check the detail of what you are trying to say.. check in the help file (highlight the function and press F1)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Location
    Crete, Greece
    Posts
    90
    That's got it. Many thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •