Full Version: Format Numbers In Query On The Fly
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
I should know this I just ran a query with dollar amounts, grouped by 3 regions - came out like this:
est ........... 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
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));
Try something like:
SELECT ..., Format(Sum(2011_REVENUE.NetRevenue), "\$#,000") AS SumOfNetRevenue
Just my 2 cents... 2cents.gif
Thanks ! There was a typo - the backslash - but got rid of that and it worked great !
Hi Steve,
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.