String format Money

Stacie
Stacie used Ask the Experts™
on
I have the following querry that give me the cost of medication as 63.9300 I would like to have this fomat instead. $69.93

SELECT     Prescription.ClientID, Prescription.CostMedication, Prescription.ReApplyDate, Prescription.[Med Rcvd Date], Prescription.Dosages,
                      Prescription.Frequency, Prescription.Notes, Medication_1.Medicaiton_Name, Prescription.MedicationID
FROM         Prescription INNER JOIN
                      Medication_1 ON Prescription.MedicationID = Medication_1.Medication_ID
WHERE     (Prescription.ReApplyDate BETWEEN @Date1 AND @Date2)
ORDER BY Prescription.ClientID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Try:
SELECT     Prescription.ClientID, '$' + CONVERT(varchar(50), CONVERT(money, Prescription.CostMedication)), Prescription.ReApplyDate, Prescription.[Med Rcvd Date], Prescription.Dosages,
                      Prescription.Frequency, Prescription.Notes, Medication_1.Medicaiton_Name, Prescription.MedicationID
FROM         Prescription INNER JOIN
                      Medication_1 ON Prescription.MedicationID = Medication_1.Medication_ID
WHERE     (Prescription.ReApplyDate BETWEEN @Date1 AND @Date2)
ORDER BY Prescription.ClientID

Open in new window

Sr. BI  Developer
Commented:
Hi,
Right click on the text box that has that value, select "Text Box Properties", select "Number", then "Currency".  and you'll get a lot of options..

see the attached image. money
Hamed NasrRetired IT Professional

Commented:
"63.9300 I would like to have this fomat instead. $69.93"

Format (varCurr, "$#.00")
Hamed NasrRetired IT Professional

Commented:
Example select query:

SELECT a.f1, a.f2, Format([f3],"$#.00") AS Expr1
FROM a;
f1      f2      Expr1
1      2      $3.00
2      2      $4.00
2      3      $5.00

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial