change number format in a SQL Report Server expression string

Basically I get how to change the properties of a cell in a SQL Server Report but I can't figure out how to do it when my expression in a cell is not limited to a single field.

Changing the properties of a cell that has an expression like:
="Total NLR USD: " & Fields!NLR_USD.Value

just leaves the number as, well, a number. How can I edit the expression to have the number in "Value" appear as currency.

The result should print:

Total NLR USD: $1,000,000.00 if value = 1000000

Thanks
pauldesAsked:
Who is Participating?
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.

mjm42Commented:
Maybe it works when you change it to:

="Total NLR USD: " & Convert(Money, Fields!NLR_USD.Value)
0
simon_kirkCommented:
Quickest way is to have:

="Total NLR USD: $" & Fields!NLR_USD.Value

This will work, though will only use $, so it depends if you need to have varying currency formats in your report.
0
pauldesAuthor Commented:
simon, tried that but the number does not format with commas 1,000,000 prints as 1000000

mjm, I get a build error on your suggestion:
 [BC30684] 'Convert' is a type and cannot be used as an expression.

Thanks
0
simon_kirkCommented:
OK, the following should give your value a format you are after:

="Total NLR USD: $" & FormatNumber(Fields!NLR_USD.Value, 2, , ,TriState.True)

Or

="Total NLR USD: " & FormatCurrency(Fields!NLR_USD.Value,,, TriState.True, TriState.True)

See these links for the full context parameters of the above two functions:

FormatNumber Function
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformatnumber.asp

FormatCurrency Function
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctformatnumber.asp
0

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
pauldesAuthor Commented:
Thanks!
0
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 SQL Server

From novice to tech pro — start learning today.

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.