Dear Mr.Allen
Pls. reply as it is very urgent. Dont think otherwise.
The below is the SQL View of the Query for your reference.
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;
Reply
Allen Browne said:
Please don't send the database. We cannot examine everyone's work: the
idea
of the groups is to give you the information for you to solve for
yourself.
Nz() will do it.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
vg said:
Sir,
For Point 1) Yes.
(2) Right aligned.
(3)Blank Coloumns: Advance Paid (Adv Paid 1st RA, 2nd RA, 3rd RA,
Prorata(75%) (Gross). These are the columns showing blank if I do not
enter
Bill Value for some RA Bills, i.e., if I enter Advance Bill Value, the
Advance Paid column in 1st RA is showing correctly but when I dont
enter
the
1st RA Bill Value & 2nd RA Bill Value and enter only Final Bill Value,
The
total of Advance Paid(Gross) [upto the Prorata Bill] is not showing in
the
Advance Paid (75%) in the Final Bill value subform.
(4) It is not working whether I take 2 fields or more.
I wish to send the Database to you as an attachment if you want. But
there
is no option for attachment here. Pls. tell me how should I attach it
for
your ready reference.
Pls. suggest.
:
Take it a bit at a time.
You are doing this in a query.
1. When you view the query, do you see the columns:
- [Advance Bill Value]
- [1st RA Bill Accounted]
- [2nd RA Bill Accounted]
- [3rd RA Bill Accounted]
Yes/No?
2. Does the data in these fields appear left-aligned (like text), or
right-aligned (like numbers/dates)?
3. Which columns are typically blank?
4. Type an expression into the Field row (in query design) that just
involves 2 fields, e.g.:
Nz([Party Payments].[Advance Bill Value],0) + Nz([1st RA Bill
Accounted],0)
Did you get that working?
When you do, you can continue adding the other fields, a bit at a
time.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Yes. I prefixed 'Nz' in all calculations but its still not working.
:
Did you try the suggestion?
I have given the default value as "zero" for all the fields. Will
you
pls.
suggest me is there any other method of getting the result.
thanks a lot sir.
:
Try:
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))
If any one of those fields is null, the result will be null.
Nz()
converts
the null to zero, so they give you a total.
(Ultimately, it's probably not a good design to have repeating
fields
like
this.)
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.
