calculating a field in a query 1

[simon551]

I'm trying to run this query in mdb. I'm new at this, but it seems like this query should calculate a balance for BalShares. But as it is, it will only produce a balance if there is data in qrySalesSales for the lotID. All of the others return blanks in the calculated fields. Interestingly, the calculation is correct for those fields it calculates. I tried changing the table joins but still no result for the calculated fields unless there is data in qrySalesSales.
Any help would be greatly appreciated.
Thanks in advance.
SELECT tblInventory.LotID, tblInventory.SecID, tblInventory.MgrID, tblInventory.Tckr, tblInventory.Description, tblInventory.Pdate, (([tblInventory.NumShares])-([qrySalesSales.NumSold])) AS BalShares, tblInventory.Price1, ([balShares]*[Price1]) AS Cost1, tblInventory.Price2, ([BalShares]*[Price2]) AS Cost2
FROM tblInventory LEFT JOIN qrySalesSales ON tblInventory.LotID = qrySalesSales.LotID;

 

[fredericofonseca]

try replacing

qrySalesSales.NumSold
by
IIf(IsNull(qrySalesSales.NumSold),0,qrySalesSales.NumSold) AS numsoldvalue

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

SELECT tblInventory.LotID, tblInventory.SecID, tblInventory.MgrID, tblInventory.Tckr, tblInventory.Description, tblInventory.Pdate, tblInventory.NumShares-Nz(qrySalesSales.NumSold,0) AS BalShares, tblInventory.Price1, ([balShares]*[Price1]) AS Cost1, tblInventory.Price2, ([BalShares]*[Price2]) AS Cost2
FROM tblInventory LEFT JOIN qrySalesSales ON tblInventory.LotID = qrySalesSales.LotID;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[simon551]

Thank you. That worked, PHV.