# Thread: CHALLANGING...somebody plz help

1. Registered User
Join Date
Jan 2004
Posts
17

## 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. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## 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
•