[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Converting number to 2-digit in MS Query


I am working on an MS Query and have the following code:

SELECT TempTable.Category, Sum(TempTable.`Dec 31, 09`), Sum(TempTable.`Dec 31, 10`), Sum(TempTable.`Jul 31, 11`)
FROM TempTable TempTable
WHERE (TempTable.Category<>'')
GROUP BY TempTable.Category

It produces what I expected and hoped for except for the extra long numbers.  Can someone tell me how to convert the number to 2 decimal places with commas as thousands separators?
0
BBlu
Asked:
BBlu
  • 3
2 Solutions
 
Jerry MillerCommented:
I would format the target cells with 2 decimals and use the ROUND function to make sure that your data is consistant.

ROUND(Sum(TempTable.`Dec 31, 10`),2)
0
 
lee555J5Commented:
If formatting the target cells is not an option in your project, try the Format command in your SQL statement. Look up the command in the help to see examples that may fit your need.

Lee
SELECT TempTable.Category, Format(Sum(TempTable.`Dec 31, 09`), "currency")
FROM TempTable TempTable
WHERE (TempTable.Category<>'')
GROUP BY TempTable.Category

Open in new window

0
 
BBluAuthor Commented:
Thank you both, when I tried the Round() function, it seemed to work (code attached).  But when I try to do the currency version, which I need I guess if I want to include commas for thousands separators, I get an error.  Any idea why?

 MS Query-Format Error
0
 
BBluAuthor Commented:
Never mind..it looks like II need to make 'currency' single quotes.. that worked.
0
 
BBluAuthor Commented:
Thank you both very much!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now