Senin, 24 Agustus 2015

RE: [MS_AccessPros] Rounding in a totals query

 

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







__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (8)

.

__,_._,___

Tidak ada komentar:

Posting Komentar