SQL Problem

[JasonLiew]

Erm.... hi,

Can anyone tell me how to code this thingy?


I used 2 different sub-form so it is quite troublesome. I have done something but it only works when ID is enter in both sub-form. ID is an unique key to identify products. If 1 sub-form is left out then it will not calculate.

I want to sum the [Order Quantity] from table [Details] minus Sum of [Quantity Receive] in table [Details2]

Balance: Sum([Details]![Order_Qty] - [Details2]![QTY Receive]) /*It is to calculate the balance*/

 

[Craig0201]

Jason,

This is a problem caused by null. Any operation performed on a Null returns null. To stop this, you need to use the function Nz(Value, OptionalReturnValue)

So assuming you need a value of 0....

Sum(Nz([Details]![Order_Qty],0) - Nz([Details2]![QTY Receive],0))

Help?

Craig

 

[JasonLiew]

Hi Craig,

Thanks for the suggestion.
Where can I put the function Nz(Value, OptionalReturnValue)?


Jason.