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;
Comments
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;
:
:
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;
: :
: :
:
: