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

Senin, 24 Agustus 2015

Re: [MS_AccessPros] Rounding in a totals query

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@hotmail.com [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