?
Solved

Format currency in SQL

Posted on 2006-04-07
9
Medium Priority
?
639 Views
Last Modified: 2009-01-21
I have the following SQL statement and it needs to be formatted in currency.  Can  this be done?

SELECT DISTINCTROW Sum([qryQtrRepAssessmts].[TotalFees]) AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;

0
Comment
Question by:gwr477
  • 4
  • 3
  • 2
9 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 total points
ID: 16403239
SELECT DISTINCTROW CCur(Sum([qryQtrRepAssessmts].[TotalFees])) AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;

Or

SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"currency") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16403241
SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"Currency") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts
0
 

Author Comment

by:gwr477
ID: 16403343
Thanks.  The CCur statement worked, but the Format statement gave me an error.  Can we drop the decimal places completely from the CCur statement?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 38

Expert Comment

by:Jim P.
ID: 16403351
Glad to be of assistance. May all your days get brighter and brighter.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 16403394
SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"$9,999") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16403430
What version of Access are you using?
You said you got an error using Format... weird

SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"$#,###") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts
0
 

Author Comment

by:gwr477
ID: 16403497
The City's suite of choice, Office 97 Pro. Neither jimpem or LPurvis Format statement works in my Access 97.  Again the CCur statement works just fine, but I would like to drop the decimal places.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16403581
It should still work in 97 - but there may be a reference problem - and causing this to fail.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16403628
You could try re-registering the libraries involved in your project.
Or for a quick fix - you might get away with just creating a new module (basFormat) - pasting this

Function fFormat(varValue, strFormat As String)
    fFormat = Format(varValue, strFormat)
End Function

Then try a query like

SELECT DISTINCTROW fFormat(Sum([qryQtrRepAssessmts].[TotalFees]),"$#,###") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question