Results 1 to 6 of 6

Thread: Query Sums

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Query Sums

    Hi, I am trying to write a query that shows Totals and Unknowns. I want to deduct the Unknowns from the Totals.
    I have used this in my query...
    Number:[Total]-[Unknown]
    But my problem is that in my both my columns there are either 0 (Zero) or blanks in the rows. How can I write my query to say if its eith blank or zero to show zero and actually perform the sum?

    Thanks,
    Jez

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    One of my new "favourite" functions... *ahem*
    Nz()
    Which stands for "Non-Zero"
    Nz([field], [value to use when blank])
    Let us know how you get on!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Georgev, Thanks for the quick reply, I have tried it and still dont get any joy.. This is what I put as my query

    Number: [Total]-Nz([Unknown],[0])

    or have I not understood your thread.

    Jez

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You need to apply the NZ to both fields, since you said EITHER field could be blank. We're assuming that by "blank" you mean Null and not "" (zero-length string)

    George,
    I always thought NZ stood for NulltoZero, but Access help doesn't say, so who knows.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    RedNeckGeek,

    I have tried what you have suggested and when I run the query it says "Enter Parameter Value & 0"
    Have I not understood what you mean as this is how I have written in it

    Number: Nz([Total],[0])-Nz([Unknown],[0])

    All I want to do where any value is either zero or blank then put a zero so that all numbers appear, similar to the excel formula of if(iserror(B1-A1),0,(B1-A1))

    Thanks,
    Jez

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Square brackets imply a field or variable - remove them from around your zeroes and you should have the solution you are looking for.
    George
    Home | Blog

Posting Permissions

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