Access Query Problem

[meinhunna]

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

 

[PHV]

SELECT PriceVale
FROM tblTrans
WHERE ItemID = 1001
UNION
SELECT 0
FROM tblTrans
WHERE NOT EXISTS (SELECT * FROM tblTrans WHERE ItemID=1001)

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