Inconsistent Output

N

Nanette

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));
 
J

John Vinson

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));
Click to expand...

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]
 
N

Nanette

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


John Vinson said:
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));
Click to expand...

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]
Click to expand...
 
Top