
Dec 5th, 2017, 03:16 PM
#1
Thread Starter
Addicted Member
[RESOLVED] Typical Calculations ...
Hi,
Using the following code in another form which is fine. But now I need to use it for another form with some calculations as below:
Need to add an extra dummy column to calculate to values of A20, A22 & A24 as PurchaseInvoiceTotal (Taxable Value (A20) + CGST Value (A22) + SGST Value (A24) AS PurchaseInvoiceTotal)
Then
PurchaseInvoiceTotal to be rounded up and A26 field to be removed in modified code.
Code:
"Select A3,A2,A4,A5,A6,A1,SUM(A15),SUM(A20),A21,SUM(A22),A23,SUM(A24),SUM(A26),Int(SUM(A26)+.5) AS AmountRounded from NewPurchasesDBTable GROUP BY A3,A2,A4,A5,A6,A1,A21,A23 ORDER BY A1 ASC"
Thanks for any support extended.

Dec 5th, 2017, 05:30 PM
#2
Thread Starter
Addicted Member
Re: Typical Calculations ...
I tried the following code but getting the error : Subqueries cannot be used in the expression (SUM(++)).
"Select A3,A2,A4,A5,A6,A1,SUM(A15),SUM(A20),A21,SUM(A22),A23,SUM(A24),SUM(A20)+SUM(A22)+SUM(A24) AS TotalInoviceValue,Int(SUM(TotalInoviceValue)+.5) AS AmountRounded from NewPurchasesDBTable GROUP BY A3,A2,A4,A5,A6,A1,A21,A23 ORDER BY A1 ASC"
The RED part in the code is throwing that error.

Dec 5th, 2017, 07:53 PM
#3
Re: Typical Calculations ...
I think that you would have to repeat the calculation rather than use the aliased column, i.e.
Code:
SUM(A20)+SUM(A22)+SUM(A24) AS TotalInoviceValue,Int(SUM(A20)SUM(A22)SUM(A24)+.5) AS AmountRounded

Dec 7th, 2017, 05:04 AM
#4
Thread Starter
Addicted Member
Re: Typical Calculations ...
Thanks a lot for your kind support. Problem resolved.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Click Here to Expand Forum to Full Width
Survey posted by VBForums.
