[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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