Inconsistent Output

Inconsistent Output

Post by TmFuZXR0Z » Mon, 01 Jan 2007 05:26:21


The following query is supposed to calcualte a date, which it actually does.
Most of the calculations work. There are a few cells that have no answer
(calculated or otherwise). I'd like to know how to get every cell to have an
answer.

SELECT DISTINCT tblParts.PartNo, tblParts.PartDesc,
tblPartDetails.BLQtyReqPD, tblPartDetails.BLPricePD, tblPartDetails.Supplier,
tblCabinetDetails.IWDateN, tblLinkPoNAndPartsAndPrN.PRNo,
tblCabinets.SubSystem, ([IWDateN]-(([SupplierLTW]+[InternalLTW])*7)) AS
NeedToOrderDate
FROM (tblParts LEFT JOIN ((tblCabinets LEFT JOIN tblCabinetDetails ON
tblCabinets.CabPN = tblCabinetDetails.CabPN) RIGHT JOIN
tblLinkPoNAndPartsAndPrN ON tblCabinets.CabPN =
tblLinkPoNAndPartsAndPrN.CabPN) ON tblParts.PartNo =
tblLinkPoNAndPartsAndPrN.PartNo) LEFT JOIN tblPartDetails ON tblParts.PartNo
= tblPartDetails.PartNo
WHERE (((tblLinkPoNAndPartsAndPrN.PONo) Is Null));
 
 
 

Inconsistent Output

Post by John Vinso » Mon, 01 Jan 2007 05:45:53

On Sat, 30 Dec 2006 12:26:21 -0800, Nanette



It would help a great deal to know WHY you're getting null values. I
suspect that one of the fields going into the calculation is itself
NULL; any arithmetic expression including a NULL value will return
NULL. You can use the NZ() function to return some suitable value if
(say) SupplierLTW is Null. I have no idea what would be "suitable" in
your business model though! If you don't know what IWDateN or
SupplierLTW is, what date would you want as the result?

John W. Vinson[MVP]

 
 
 

Inconsistent Output

Post by TmFuZXR0Z » Mon, 01 Jan 2007 07:37:07

You're right, there was missing data going into the calculation. I'll try the
NZ function.