Format currency in SQL

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;

gwr477Asked:
Who is Participating?
 
Jim P.Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"Currency") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts
0
 
gwr477Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
 
Jim P.Commented:
SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"$9,999") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
gwr477Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
It should still work in 97 - but there may be a reference problem - and causing this to fail.
0
 
Leigh PurvisDatabase DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.