how to set a default value zero"0" in Queries

[bronson_mech]

hi fellas,



i have problem, i created a query for calculating Sum of Quantities its working perfect..... but i want the default value as zero, so far in some cell it is blank "instead of blank cell i want default value zero" pleas help me in this issue................

[pbaldy]

That's a little vague, but try the Nz() function.

[bronson_mech]

yeah tried with NZ() fn..... its breaking the summary..........

Before NZ() Fn
S.NO Sum
1 3
2 3
3 7

After NZ() Fn

S.No Sum
1 2
1 1
2 1
2 1
2 1
3 5
3 2

[pbaldy]

Would it be possible to see how you're using it?

[bronson_mech]

yup sure.........

Received Qty, Bal Qty, Issued Qty & Redurn Qty & Stock All are Calculated Query.......



and for calculating sum in query is as follow............

[RuralGuy]

How about showing Paul the SQL for the query?

[bronson_mech]

SQL Query

SELECT QryReturnedDetails.ProductID, Sum(QryReturnedDetails.ReturnQty) AS SumOfReturnQty
FROM QryReturnedDetails
GROUP BY QryReturnedDetails.ProductID;

[RuralGuy]

I don't see the Nz() function being used.

[bronson_mech]

SELECT QryReturnedDetails.ProductID, Sum(QryReturnedDetails.ReturnQty) AS SumOfReturnQty, Nz([ReturnQty],0) AS ReturnQty
FROM QryReturnedDetails
GROUP BY QryReturnedDetails.ProductID, Nz([ReturnQty],0);

[RuralGuy]

Back to you Paul. 8^)

[bronson_mech]

thanks A lot rural guy.......... i got it....... so far i had been using that NZ()fn in wrong Query............ NOw i corrected .......hehehehehehe......... thanx.......

[RuralGuy]

Glad you got it sorted and I should not have stomped on Paul's assisting. Sorry Paul!

[pbaldy]

No worries Allan. I've got family in town so have only been here intermittently anyway.