Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NZ function (Access 2000)

    I have a query that substracts two subqueries.My problem is that when the second query has no values, then
    the results from the first query are also not shown. I have tried to place NZ in order to avoid that :
    NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity])

    But nevertheless the results from the first query are not shown and obviously the NZ function is not used properly by me.
    Can somebody help me ?

    My entire query is the following :

    SELECT InVa.grade, InVa.size, InVa.SumOfcartons AS [In], OutVa.SumOfcartons AS Out, NZ([InVa.SumOfcartons]-[OutVa.SumOfcartons]) AS stock, InVa.SumOfQuantity, OutVa.SumOfQuantity, NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity]) AS items
    FROM OutVa INNER JOIN InVa ON OutVa.grade = InVa.grade;

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: NZ function (Access 2000)

    You have to wrap each value in an Nz function, not just the entire expression, and do give the Nz function a default value. When dealing with a table field, you might be able to get away with no default but if you're using a calculated value instead of a field, the query engine may not be able to guess at the correct default and it's better programming practice not to skip it anyhow. So change your expression to this:

    NZ([InVa.SumOfQuantity],0)-Nz([OutVa.SumOfQuantity],0)
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Happy New Year

    Happy New Year and may all you wishes come true

Posting Permissions

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