Access Query Problem

[PankajBanga]

I have following query written in my Access DB.

SELECT PriceVale
FROM tblTrans
WHERE ItemID = 1001

My problem is I want this query to return 0 if record is not found in the table. Apparently it returns empty field.

I tried using Nz functions as follows, but still it doesn't return 0

SELECT Nz(PriceVale,0)
FROM tblTrans
WHERE ItemID = 1001

Please help. Thanks

 

[Pat Hartman]

Depending on where you are using this query, you may be able to replace it with a DLookup().

DLookup("PriceVale", "tblTrans", "ItemID = 1001")

 

[aleb]

If you will join your table where there is no such record to the table where the this itemid is present you will get the needed result.
like this:
SELECT Tablewherepresent.Itemid, Sum(nz([pricevalue])) AS Total
FROM Tablewherepresent LEFT JOIN Tablewherenotpresent ON Tablewherepresent.Itemid = Tablewherenotpresent.Fitemid
GROUP BY Tablewherepresent.Itemid;