Full Version: Format Numbers In Query On The Fly
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
bakersburg9
I should know this 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));
theDBguy
Hi,

Try something like:

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

(untested)
Just my 2 cents... 2cents.gif
bakersburg9
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 !

cool.gif

Steve

theDBguy
Hi Steve,

yw.gif

Glad to hear you got it to work. Good luck with your project.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.