UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Format Numbers In Query On The Fly, Office 2010    
 
   
bakersburg9
post Jan 12 2012, 02:29 PM
Post #1

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
I should know this (IMG:style_emoticons/default/confused.gif) I just ran a query with dollar amounts, grouped by 3 regions - came out like this:

West ........... 301098.7500000001
East............. 2769520.85
South........... 722571.22

In the syntax of my query, how do I add the word Format and get xxx,xxx - commas separting thousands, no decimels - I know I can accomplish this by going into the design grid and right clicking, then going into format, but I want to save that step

CODE
SELECT DISTINCT Areas.AreaID, Areas.AreaName, Sum(2011_REVENUE.NetRevenue) AS SumOfNetRevenue
FROM ((2011_REVENUE INNER JOIN (Areas INNER JOIN Agents ON Areas.AreaID = Agents.AreaID) ON (2011_REVENUE.AGENT = Agents.agent) AND (2011_REVENUE.PRIN = Agents.prin) AND (2011_REVENUE.SYS = Agents.sys)) INNER JOIN (ServiceCodes LEFT JOIN Accounts ON ServiceCodes.AcctNumID = Accounts.AcctNumID) ON (2011_REVENUE.SC_Desc = ServiceCodes.SC_Desc) AND (2011_REVENUE.ServiceCode = ServiceCodes.ServiceCode)) LEFT JOIN ServiceType ON 2011_REVENUE.NATURE_OF_SERV_GLS = ServiceType.ServeTypeID
GROUP BY ServiceCodes.Phone, Areas.AreaID, Areas.AreaName
HAVING (((ServiceCodes.Phone)=True) AND ((Areas.AreaID) In (65,95,108)) AND ((Sum(2011_REVENUE.NetRevenue))<>0));
Go to the top of the page
 
+
theDBguy
post Jan 12 2012, 02:34 PM
Post #2

Access Wiki and Forums Moderator
Posts: 47,919
From: SoCal, USA
Hi,

Try something like:

SELECT ..., Format(Sum(2011_REVENUE.NetRevenue), "\$#,000") AS SumOfNetRevenue
FROM...

(untested)
Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
bakersburg9
post Jan 12 2012, 04:04 PM
Post #3

UtterAccess Ruler
Posts: 4,237
From: Downey, CA
QUOTE (theDBguy @ Jan 12 2012, 07:34 PM) *
SELECT ..., Format(Sum(2011_REVENUE.NetRevenue), "\$#,000") AS SumOfNetRevenue FROM... (untested)


Thanks ! There was a typo - the backslash - but got rid of that and it worked great !

(IMG:style_emoticons/default/cool.gif)

Steve
Go to the top of the page
 
+
theDBguy
post Jan 12 2012, 04:52 PM
Post #4

Access Wiki and Forums Moderator
Posts: 47,919
From: SoCal, USA
Hi Steve,

(IMG:style_emoticons/default/yw.gif)

Glad to hear you got it to work. Good luck with your project.
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 19th May 2013 - 10:23 PM