Calcuation Error in Select Query

[cvgope]

I have created a database for Payments entry for various parties in which I have created a Select Query for obtaining the Bill Details (Advance, Running & Final Bills) such as Bill value, Advance Paid, Bill Accounted, TDS, WCT, Balance Payable. I have given calculations based on Bill Value(s) in this query for all these bills (Advance, Running & Final Bills).

When I enter Bill Values for all Advance, Running Bills details only It is showing the Value in "Advance Paid" coloumn. Whereas if I enter the Bill Values in only in Advance Bill, there is no display in "Advance Paid" coloumn in the Final Bill Value section. I have given the calculation for obtaining the "Advance Paid" coloumn in the Final Bill section like this:
Adv Paid 3rd RA(Gross): CCur([Party Payments].[Advance Bill Value])+([1st RA Bill Accounted])+([2nd RA Bill Accounted])+([3rd RA Bill Accounted]).

Pls help on this.

[Stewart Ross]

When adding values you need to be careful about null entries - these can cause errors if a null is fed to a type conversion function such as CCur, for example, and nulls do not fit with arithmetic or other calculated operations - the null propagates, and adding a null to anything results in a null. Your CCur (type conversion to Currency) is covering the first field only - the others are not in its scope.

To avoid this, use Nz on each field and enclose the whole calculation within the CCur:

Adv Paid 3rd RA(Gross): CCur(Nz([Party Payments].[Advance Bill Value], 0) + Nz([1st RA Bill Accounted], 0)+Nz([2nd RA Bill Accounted], 0)+Nz([3rd RA Bill Accounted], 0))

Nz returns the specified text if a null value is encountered - 0 in this case. Null values in numeric fields or textboxes will not propagate if you use Nz to replace them with 0s or some other value as appropriate.

-Stewart

[cvgope]

Sir, I tried what you said by prefixing/using NZ, but still the problem remains the same. Is there any other way of getting the result?

[Stewart Ross]

It would help if you could post the up-to-date statement you are using for your calculation. It would also help if you could post the SELECT statement you are referring to in post 1; I am not sure what relation these have to each other, but the more information you can give us to work on the better.

-Stewart

[cvgope]

Sir, a Select Query containing Details such as Bill value, Advance Paid, Bill Accounted, TDS, WCT,Balance Payable for Adv.Bill, 1st RA Bill, 2nd RA Bill, 3rd RA Bill & Final Bill.

I have given calculations based on Bill Value(s) for obtaining Bill Accounted, TDS, WCT & Balance Payable for all the Bills(1st, 2nd, 3rd RA & Final Bills).

I am getting all the calculation right except in the "Advance Paid" coloumn where I am supposed to get the total of all Advances Paid (beginning from Advance Bill to Final Bill). If I enter bill values in all corresponding Bill Sections (created individual Subforms for all RA Bills & Final Bills) only, the Total is displaying in all the subforms whereas if I enter Bill values only for 2 Bills (For example in Advance & 2nd RA Bill), the Total of Advances paid till the previous bill is not showing in the coloumn "Advance Paid". The following is the calculation I have given for each bill(s):

The SELECT Query is named as Party Payment Details. The following is the SQL Query: (Although the entire content below is given in one query, I have divided the query bill wise here for easy understanding, if i am right).

SELECT [Party Payments].OrderID, [Party Payments].[Advance Bill Value], CCur(Nz([Party Payments].[Advance Bill Value],0)*2.266/100) AS [TDS on Adv Bill], CCur(Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on Adv Bill],0)) AS [Net Adv Payable], [Party Payments].[Cheq/DD No], [Party Payments].[Cheq/DD Date],

[Party Payments].[1st RA Bill No], [Party Payments].[1st RA Bill Value], CCur(Nz([Advance Bill Value],0)) AS [Adv Paid(Gross)], CCur(Nz([Party Payments].[1st RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)) AS [1st RA Bill Accounted], CCur(Nz([1st RA Bill Accounted],0)*2.266/100) AS [TDS on 1st RA], CCur(Nz([1st RA Bill Accounted],0)*4/100) AS [WCT on 1st RA], CCur(Nz([1st RA Bill Accounted],0)*5/100) AS [5% on 1st RA], CCur(Nz([Party Payments].[1st RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on 1st RA],0)-Nz([WCT on 1st RA])-Nz([5% on 1st RA],0)) AS [1st RA Payable], [Party Payments].[Cheq/DD No(1st RA)], [Party Payments].[Cheq/DD Date(1st RA)],

[Party Payments].[2nd RA Bill No], [Party Payments].[2nd RA Bill Value], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)) AS [Adv Paid 1st RA(Gross)], CCur(Nz([Party Payments].[2nd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([1st RA Bill Accounted],0)) AS [2nd RA Bill Accounted], CCur(Nz([2nd RA Bill Accounted],0)*2.266/100) AS [TDS on 2nd RA], CCur(Nz([2nd RA Bill Accounted],0)*4/100) AS [WCT on 2nd RA], CCur(Nz([2nd RA Bill Accounted],0)*5/100) AS [5% on 2nd RA], CCur(Nz([Party Payments].[2nd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on 2nd RA],0)-Nz([WCT on 2nd RA])-Nz([5% on 2nd RA],0)-Nz([1st RA Bill Accounted],0)) AS [2nd RA Payable], [Party Payments].[Cheq/DD No(2nd RA)], [Party Payments].[Cheq/DD Date(2nd RA)],

[Party Payments].[3rd RA Bill No], [Party Payments].[3rd RA Bill Value], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)+Nz([2nd RA Bill Accounted],0)) AS [Adv Paid 2nd RA(Gross)], CCur(Nz([Party Payments].[3rd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)) AS [3rd RA Bill Accounted], CCur(Nz([3rd RA Bill Accounted],0)*2.266/100) AS [TDS on 3rd RA], CCur(Nz([3rd RA Bill Accounted],0)*4/100) AS [WCT on 3rd RA], CCur(Nz([3rd RA Bill Accounted],0)*5/100) AS [5% on 3rd RA], CCur(Nz([Party Payments].[3rd RA Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([TDS on 3rd RA],0)-Nz([WCT on 3rd RA],0)-Nz([5% on 3rd RA],0)) AS [3rd RA Payable], [Party Payments].[Cheq/DD No(3rd RA)], [Party Payments].[Cheq/DD Date(3rd RA)],

[Party Payments].[Final Bill No], [Party Payments].[Final Bill Value(75%)], [Party Payments].[Excess Qty], CCur(Nz([Party Payments].[Final Bill Value(75%)],0)-Nz([Party Payments].[Excess Qty],0)) AS [Amt Before Prorata], CCur(Nz([Amt Before Prorata],0)*75/100) AS [Pro Rata Value], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)+Nz([2nd RA Bill Accounted],0)+Nz([3rd RA Bill Accounted],0)) AS [Adv Paid 3rd RA(Gross)], CCur(Nz([Pro Rata Value],0)-Nz([Adv Paid 3rd RA(Gross)],0)) AS [Pro Rata Accounted], CCur(Nz([Pro Rata Accounted],0)*2.266/100) AS [TDS on Final Bill(75%)], CCur(Nz([Pro Rata Accounted],0)*4/100) AS [WCT on Final Bill(75%)], CCur(Nz([Pro Rata Accounted],0)*5/100) AS [5% on Final Bill(75%)], CCur(Nz([Pro Rata Accounted],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on Final Bill(75%)],0)-Nz([WCT on Final Bill(75%)],0)-Nz([5% on Final Bill(75%)],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([3rd RA Bill Accounted],0)-Nz([Excess Qty],0)) AS [Final Bill(75%) Payable], [Party Payments].[Cheq/DD No(Pro-rata)], [Party Payments].[Cheq/DD Date(Pro-rata)],

[Party Payments].[Final Bill Value], [Party Payments].[Audit Deductions], [Party Payments].[Other Misc Deductions], CCur(Nz([Party Payments].[Advance Bill Value],0)+Nz([1st RA Bill Accounted],0)+Nz([2nd RA Bill Accounted],0)+Nz([3rd RA Bill Accounted],0)+Nz([Pro Rata Accounted],0)) AS [Adv Paid 75%(Gross)], CCur(Nz([Party Payments].[Final Bill Value],0)-Nz([Pro Rata Accounted],0)-Nz([3rd RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([1st RA Bill Accounted],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([Party Payments].[Audit Deductions],0)-Nz([Party Payments].[Other Misc Deductions],0)) AS [Final Bill Accounted], CCur(Nz([Final Bill Accounted],0)*2.266/100) AS [TDS on Final Bill], CCur(Nz([Final Bill Accounted],0)*4/100) AS [WCT on Final Bill], CCur(Nz([Final Bill Accounted],0)*5/100) AS [5% on Final Bill], CCur(Nz([Party Payments].[Final Bill Value],0)-Nz([Party Payments].[Advance Bill Value],0)-Nz([TDS on Final Bill],0)-Nz([WCT on Final Bill],0)-Nz([5% on Final Bill],0)-Nz([1st RA Bill Accounted],0)-Nz([2nd RA Bill Accounted],0)-Nz([3rd RA Bill Accounted],0)-Nz([Pro Rata Accounted],0)-Nz([Party Payments].[Audit Deductions],0)-Nz([Party Payments].[Other Misc Deductions],0)) AS [Balance Payable], [Party Payments].[Cheq/DD No(Final)], [Party Payments].[Cheq/DD Date(Final)]
FROM Orders INNER JOIN [Party Payments] ON Orders.OrderID = [Party Payments].OrderID;

[cvgope]

Dear Mr.Stewart Ross

help me out with this problem. Its urgent. Dont think otherwise.

[cvgope]

I will be greatful if anybody sort this out and show me the solution. It is very urgent.