ACCESS FEED: Re: [MS_AccessPros] Rounding in a totals query

Selasa, 25 Agustus 2015

Re: [MS_AccessPros] Rounding in a totals query

John,

LNETOT_31and FRTAMT_31 are both doubles and come from my ERP system based on SQL server. This table contains records from our Invoice Master. LNETOT_31 sums the amounts from the invoice detail records and FRTAMT_31 is the freight amount for shipping the products on the invoice. All values are stored as positive numbers regardless of whether the transaction is an invoice or a credit memo where products are returned. The different is the document type of CR for a credit memo or CU for a customer order. I didn't know any other way to get the minus sign in front of the negative numbers so that when summing the totals, the credit memo amounts are negative. I don't know what the difference is between what I was doing yesterday and today is but this SQL works.:

SELECT "521" AS Company, "RSI" AS Location, "0" AS Function, 243121 AS Account, "000" AS Activity, "0" AS Product, IIf([CustID_31]="CTL","774","751") AS Interco, Round(Sum(IIf([stype_31]="CR",[lnetot_31]*-1,[lnetot_31])+(IIf([stype_31]="CR",[FRTAMT_31]*-1,[FRTAMT_31]))),2) AS Debit, "CMI Intercompany AP" AS [Line Description]
FROM [Invoice Master]
WHERE ((([Invoice Master].CustID_31)>"a") AND (([Invoice Master].Invdte_31) Between [Forms]![frmAcctReports]![Start Date] And [Forms]![frmAcctReports]![End Date]))
GROUP BY "521", "RSI", 243121, "000", "0", IIf([CustID_31]="CTL","774","751"), "CMI Intercompany AP", "0";

If there is a better way to handle the negative amounts for the credit memos, I'd love to learn. I'm getting much better at debugging my syntax. Your assistance late last week helped me to figure out that if I do a debug.print on my VBA SQL syntax then it is easier for me to find missing " , ) and etc. I have never had any formal training in Access but I have learned so much and done some really cool things with help from this group. I can never express how much I appreciate those of you who help us and teach us. I'm using what I've learned to pay it forward to others as well by sharing some of the databases I've created to help my wife who is a teacher with other teachers and helping my daughter automate some of the processes she does at work with Access. Many thanks from all of us to all the Pro's that help us on this group.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Doyce-

You don't say what is the data type of Inetot_31 or FRTAMT_31. You make the problem worse by using *-1 to get the negative instead of simply prefixing a minus sign. When you multiply inside a Sum, Access converts everything to floating point. That's probably why you're getting strange extra digits. If that simple fix doesn't do it, wrap the Sum in:

CCur(CLng( Sum( ... ) * 100) / 100)

That will "round" to pennies.

John Viescas

Brittany, France

Sent from my iPad

On Aug 25, 2015, at 01:27, Duane Hookom duanehookom@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Doyce,
I saw your SQL. I didn't see anywhere in the SQL that you used Round().

"Can you provide the full sql of the query where you were attempting to use Round()?"

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 24 Aug 2015 13:53:54 -0700
Subject: RE: [MS_AccessPros] Rounding in a totals query

Duane,

Rounding the sum would be fine. Please scroll down. I posted the SQL.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Doyce,
Were you attempting to Round() the sum or sum the Round()? Can you provide the full sql of the query where you were attempting to use Round()?

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 24 Aug 2015 12:15:04 -0700
Subject: Re: [MS_AccessPros] Rounding in a totals query

Crystal,

Thanks for your input, however I tried that first and it just wouldn't round. It returned numbers like 49,521.650000001. When I use CCur, my result is only showing 2 decimal places. I'm just trying to get rid of the last 7 decimal places.

Doyce

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

Hi Doyce,

the Currency Data Type holds 4 decimal places. If you want to round to 2:

Round(expression, 2)

warm regards,
crystal

Microsoft MVP Virtual Conference Presentation on Access Web Apps, and linking to SQL Azure from Access, Excel, and SSMS (53 min video)
https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps

~ be awesome today ~

On 8/24/2015 12:50 PM, winberry.doyce@... [MS_Access_Professionals] wrote:

Sure Duane. I appreciate you taking a look at it.

SELECT "521" AS Company, "RSI" AS Location, "0" AS Function, 243121 AS Account, "000" AS Activity, "0" AS Product, IIf([CustID_31]="CTL","774","751") AS Interco, Sum(IIf([stype_31]="CR",[lnetot_31]*-1,[lnetot_31])+((IIf([stype_31]="CR",[FRTAMT_31]*-1,[FRTAMT_31])))) AS Debit, "CMI Intercompany AP" AS [Line Description]
FROM [Invoice Master]
WHERE ((([Invoice Master].CustID_31)>"a") AND (([Invoice Master].Invdte_31) Between [Forms]![frmAcctReports]![Start Date] And [Forms]![frmAcctReports]![End Date]))
GROUP BY "521", "RSI", 243121, "000", "0", IIf([CustID_31]="CTL","774","751"), "CMI Intercompany AP", "0";

I found that If I do this I can get it in currency format:
SELECT "521" AS Company, "RSI" AS Location, "0" AS Function, 243121 AS Account, "000" AS Activity, "0" AS Product, IIf([CustID_31]="CTL","774","751") AS Interco, CCur(Sum(IIf([stype_31]="CR",[lnetot_31]*-1,[lnetot_31])+((IIf([stype_31]="CR",[FRTAMT_31]*-1,[FRTAMT_31]))))) AS Debit, "CMI Intercompany AP" AS [Line Description]
FROM [Invoice Master]
WHERE ((([Invoice Master].CustID_31)>"a") AND (([Invoice Master].Invdte_31) Between [Forms]![frmAcctReports]![Start Date] And [Forms]![frmAcctReports]![End Date]))
GROUP BY "521", "RSI", 243121, "000", "0", IIf([CustID_31]="CTL","774","751"), "CMI Intercompany AP", "0";

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Can you please provide the full SQL view of the totals query?

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 24 Aug 2015 09:52:11 -0700
Subject: [MS_AccessPros] Rounding in a totals query

Friends,
Is there any way to get this to round to 2 decimal places in a totals query?
Credit: IIf([stype_31]="CR",[lnetot_31]*-1,[lnetot_31])
I've tried Credit: Round(IIf([stype_31]="CR",[lnetot_31]*-1,[lnetot_31]),2).
I've tried rounding lnetot_31 each time it is used but still get a result like 431277.6600000001.
Doyce