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