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

Selasa, 25 Agustus 2015

RE: [MS_AccessPros] Rounding in a totals query

Duane,

I don't know what I was doing different yesterday from today, but this is working:

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";

Doyce

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> 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