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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"Currency") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
Jim P.Commented:
SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"$9,999") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;
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
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.
Leigh PurvisDatabase DeveloperCommented:
It should still work in 97 - but there may be a reference problem - and causing this to fail.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.