NULL Valued Inner SQL Query

[monzur0010]

#Start================================================
#Block 1
SELECT G.group_name, P.part_no,

#Block 2
(((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.edate <#8/9/2008# AND Ri.IN_OUT='IN' AND Di.part_no=P.part_no))-
((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.edate <#8/9/2008# AND Ri.IN_OUT='OUT' AND Di.part_no=P.part_no))) AS OnHand,

#Block 3
((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.code_type='SHOP' AND Ri.edate BETWEEN #8/9/2008# AND #9/16/2008# AND Ri.IN_OUT='IN' AND Di.part_no=P.part_no)) AS ReceiedFromShop,

#Block 4
((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.code_type<>'SHOP' AND Ri.edate BETWEEN #8/9/2008# AND #9/16/2008# AND Ri.IN_OUT='IN' AND Di.part_no=P.part_no)) AS OnReceied,

#Block 5
((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.edate BETWEEN #8/9/2008# AND #9/16/2008# AND Ri.IN_OUT='OUT' AND Di.part_no=P.part_no AND code_type='SHOP')) AS SentShop,

#Block 6
(((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.edate <#9/16/2008# AND Ri.IN_OUT='IN' AND Di.part_no=P.part_no))-
((SELECT SUM(qty) FROM rec_detail AS Di, receive AS Ri WHERE Di.rec_id=Ri.rec_id AND Ri.edate <#9/16/2008# AND Ri.IN_OUT='OUT' AND Di.part_no=P.part_no))) AS OnHandReport

#Block 7
FROM prod_group AS G, prod_list AS P, rec_detail AS D

WHERE G.group_code = P.group_code AND P.part_no=D.part_no

GROUP BY G.group_name, P.part_no

ORDER BY G.group_name ASC, P.part_no ASC
#End================================================

Here i have multiple Inner Query. Here the Bock2 and Block6 Query will return 2 value and then Deduct, but from these 2 value 1 or both could be Null value,
if any one of them is null then, i will not get 2nd value, but i need both
NZ is works in Access to remove Null but, when i use it on VB6.0 it says function is undefined, so how can i use my query, or can anyone solve it with my Result Set

Pls. do something for me, otherwise my program will take to much time generate the Report.

[davide++]

Hi all.

Access provides the IsNull function to manage NULL values. You can use it with the Iif function, so you could write something like this:
IIf(IsNull(expr), VALUE_IF_IT_IS_NULL, expr).
See the documentation about these functions and try to rewrite your query using them.

[monzur0010]

Thanks buddy

it works. thank you very much

Hi all.

Access provides the IsNull function to manage NULL values. You can use it with the Iif function, so you could write something like this:
IIf(IsNull(expr), VALUE_IF_IT_IS_NULL, expr).
See the documentation about these functions and try to rewrite your query using them.