NZ function

piligrim

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
FROM OutVa INNER JOIN InVa ON OutVa.grade = InVa.grade;

cougar98

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
: FROM OutVa INNER JOIN InVa ON OutVa.grade = InVa.grade;
:
:

matgrohlich

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.

Therefore your query should read:

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
: : FROM OutVa INNER JOIN InVa ON OutVa.grade = InVa.grade;
: :
: :
:
: