# NZ function

NZ Function

I have a query that substracts two subqueries.My problem is that when the second query has no values, then
the results from the first query are also not shown. I have tried to place NZ in order to avoid that :
NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity])

But nevertheless the results from the first query are not shown and obviously the NZ function is not used properly by me.
Can somebody help me ?

My entire query is the following :

SELECT InVa.grade, InVa.size, InVa.SumOfcartons AS [In], OutVa.SumOfcartons AS Out, NZ([InVa.SumOfcartons]-[OutVa.SumOfcartons]) AS stock, InVa.SumOfQuantity, OutVa.SumOfQuantity, NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity]) AS items

• I've just joined this group & see you haven't had a reply - I have found with nz that it helps by adding a "0" to the end of each calculation. Also check you properties in the table where the data initially comes from and make sure both fields are Numbers.

Your query calc should look like this:

NZ([InVa.SumOfQuantity])-nz([OutVa.SumOfQuantity])+0

Another function that has worked for me in the past is to include a Sum function in the calculation

Sum (NZ([InVa.SumOfQuantity])-nz([OutVa.SumOfQuantity])+0)

Hope this helps...

Dean

:
: NZ Function
:
:
: I have a query that substracts two subqueries.My problem is that when the second query has no values, then
: the results from the first query are also not shown. I have tried to place NZ in order to avoid that :
: NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity])
:
: But nevertheless the results from the first query are not shown and obviously the NZ function is not used properly by me.
: Can somebody help me ?
:
: My entire query is the following :
:
: SELECT InVa.grade, InVa.size, InVa.SumOfcartons AS [In], OutVa.SumOfcartons AS Out, NZ([InVa.SumOfcartons]-[OutVa.SumOfcartons]) AS stock, InVa.SumOfQuantity, OutVa.SumOfQuantity, NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity]) AS items
:
:

• Hi

Nz function will enter a value into a field if the value you want is null. You need to specifiy what value you want entered.

NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity], 0)

This will enter a zero into the field if you calculation returns a null value.
=============

: I've just joined this group & see you haven't had a reply - I have found with nz that it helps by adding a "0" to the end of each calculation. Also check you properties in the table where the data initially comes from and make sure both fields are Numbers.
:
: Your query calc should look like this:
:
: NZ([InVa.SumOfQuantity])-nz([OutVa.SumOfQuantity])+0
:
: Another function that has worked for me in the past is to include a Sum function in the calculation
:
: Sum (NZ([InVa.SumOfQuantity])-nz([OutVa.SumOfQuantity])+0)
:
: Hope this helps...
:
: Dean
:
:
:
: :
: : NZ Function
: :
: :
: : I have a query that substracts two subqueries.My problem is that when the second query has no values, then
: : the results from the first query are also not shown. I have tried to place NZ in order to avoid that :
: : NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity])
: :
: : But nevertheless the results from the first query are not shown and obviously the NZ function is not used properly by me.
: : Can somebody help me ?
: :
: : My entire query is the following :
: :
: : SELECT InVa.grade, InVa.size, InVa.SumOfcartons AS [In], OutVa.SumOfcartons AS Out, NZ([InVa.SumOfcartons]-[OutVa.SumOfcartons]) AS stock, InVa.SumOfQuantity, OutVa.SumOfQuantity, NZ([InVa.SumOfQuantity]-[OutVa.SumOfQuantity]) AS items