asked on

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;

ASKER CERTIFIED SOLUTION

Jim P.

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Leigh Purvis

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

gwr477

ASKER

Thanks. The CCur statement worked, but the Format statement gave me an error. Can we drop the decimal places completely from the CCur statement?

Jim P.

Glad to be of assistance. May all your days get brighter and brighter.

Jim P.

SELECT DISTINCTROW Format(Sum([qryQtrRepAssessmts].[TotalFees]),"$9,999") AS [Sum Of TotalFees]
FROM qryQtrRepAssessmts;

Leigh Purvis

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

gwr477

ASKER

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 Purvis

It should still work in 97 - but there may be a reference problem - and causing this to fail.

Leigh Purvis

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