PLEASE HELP!!! NULL field ?

[Joan]

I am still trying to figure out how to handle this.
I have an ItemDetails table and a sales order details table.

I need to do a stock short report but my problem is that the sale order might have a part that does not yet exist in the item details table (hasn't been received before).

I have a query:

SELECT tblSalelNoParts.SaleNo, Sum(tblSaleNoParts.Qty) AS SumOfQty, tblSaleNoParts.PartNo, tblSaleNoParts.EndType, Sum(tblItemDetail.Qty) AS SumOfQty1, IIf(Sum([tblItemDetail]![Qty])=Null,Sum([tblSaleNoParts]![Qty])-0,Sum([tblSaleNoParts]![Qty])-Sum([tblItemDetail]![Qty])) AS StockShortTest
FROM tblItemDetail RIGHT JOIN tblSaleNoParts ON tblItemDetail.ItemNo = tblSaleNoParts.PartNo
GROUP BY tblSalelNoParts.SalelNo, tblSalelNoParts.PartNo, tblSaleNoParts.EndType;

This gives me all parts from the SalesOrder and subtracts the qty from the qty on ItemDetails table. Where my problem is if the part isn't in the itemdetails table yet then itemdetails.Qty is null so I thought the IIf statement would handle that by subtracting 0 and giving me the result in StockShortTest but it just gives me a NULL result. Can I handle this here? Or do I need to create a 'dummy record' in the item details table with qty 0 (I don't want to do this as probably not good db design).

As always any help is appreciated.
Joan

 

[Dan Artuso]

Hi,
You can never check for something being '=' null.
because nothing is ever equal to Null.

In your case, use the NZ function. In other cases you use the syntax
[field] Is Null as criteria.

HTH
Dan Artuso, MVP

I am still trying to figure out how to handle this.
I have an ItemDetails table and a sales order details table.

I need to do a stock short report but my problem is that the sale order

might have a part that does not yet exist in the item details table (hasn't
been received before).

I have a query:

SELECT tblSalelNoParts.SaleNo, Sum(tblSaleNoParts.Qty) AS SumOfQty,

tblSaleNoParts.PartNo, tblSaleNoParts.EndType, Sum(tblItemDetail.Qty) AS
SumOfQty1,
IIf(Sum([tblItemDetail]![Qty])=Null,Sum([tblSaleNoParts]![Qty])-0,Sum([tblSa
leNoParts]![Qty])-Sum([tblItemDetail]![Qty])) AS StockShortTest

FROM tblItemDetail RIGHT JOIN tblSaleNoParts ON tblItemDetail.ItemNo = tblSaleNoParts.PartNo
GROUP BY tblSalelNoParts.SalelNo, tblSalelNoParts.PartNo, tblSaleNoParts.EndType;

This gives me all parts from the SalesOrder and subtracts the qty from the

qty on ItemDetails table. Where my problem is if the part isn't in the
itemdetails table yet then itemdetails.Qty is null so I thought the IIf
statement would handle that by subtracting 0 and giving me the result in
StockShortTest but it just gives me a NULL result. Can I handle this here?
Or do I need to create a 'dummy record' in the item details table with qty 0
(I don't want to do this as probably not good db design).

 

[Ted]

The problem is with the part of your statement that
checks to see if a field is null. Since Null is not a
value to access (it just means nothing has been entered -
the field is blank) you cannot use the = statement to
check for it. Instead, try replacing your iif statement
with the following which uses the Nz() function which
will return 0 for a null value, such as the following.

Sum([tblSaleNoParts]![Qty])-(Sum(Nz([tblItemDetail]!
[Qty],0))

Hope that helps. Post back if it doesn't work.

-Ted

-----Original Message-----
I am still trying to figure out how to handle this.
I have an ItemDetails table and a sales order details table.

I need to do a stock short report but my problem is that

the sale order might have a part that does not yet exist
in the item details table (hasn't been received before).

I have a query:

SELECT tblSalelNoParts.SaleNo, Sum(tblSaleNoParts.Qty)

AS SumOfQty, tblSaleNoParts.PartNo,
tblSaleNoParts.EndType, Sum(tblItemDetail.Qty) AS
SumOfQty1, IIf(Sum([tblItemDetail]![Qty])=Null,Sum
([tblSaleNoParts]![Qty])-0,Sum([tblSaleNoParts]![Qty])-Sum
([tblItemDetail]![Qty])) AS StockShortTest

FROM tblItemDetail RIGHT JOIN tblSaleNoParts ON

tblItemDetail.ItemNo = tblSaleNoParts.PartNo

GROUP BY tblSalelNoParts.SalelNo,

tblSalelNoParts.PartNo, tblSaleNoParts.EndType;

This gives me all parts from the SalesOrder and

subtracts the qty from the qty on ItemDetails table.
Where my problem is if the part isn't in the itemdetails
table yet then itemdetails.Qty is null so I thought the
IIf statement would handle that by subtracting 0 and
giving me the result in StockShortTest but it just gives
me a NULL result. Can I handle this here? Or do I need
to create a 'dummy record' in the item details table with
qty 0 (I don't want to do this as probably not good db
design).