Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Post Unanswered: CHALLANGING...somebody plz help

    I'm using two tables sales and purchase.

    to get the current stock i'm using the query...

    SELECT Nz(Sum(Purchase.QTY),0) - Nz(Sum(Sales.QTY),0) AS Stock
    FROM Purchase, Sales;

    It gives the perfect value when both the tables has some values, but fails to get the value when any of the tables Qty returns nothing.

    i.e nothing - 10(some value) = nothing

    I'm also converting null to zero using Nz Function but then also the problem is not solved. (it should give me -10)

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Cool try using the iif statement

    SELECT
    iif(Nz(Sum(Purchase.QTY),0)<0 ,0,Nz(Sum(Purchase.QTY),0)) - iif(Nz(Sum(Sales.QTY),0)<0,0,Nz(Sum(Sales.QTY),0)) AS Stock
    FROM Purchase, Sales


    what this does is :
    if the value of Nz(Sum(Purchase.QTY),0) is smaler then zero
    then it wil return 0
    else it will return the value

Posting Permissions

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