Format Part of an Expression?

Question

Post reply

Format Part of an Expression?

2Tall

SSChampion

Points: 14644
More actions
June 3, 2008 at 3:22 am

#188498

Hi I have an expression on a report that takes it's values from 2 fields. I would like to format the second part of the expression to display the £ symbol (Fields!CustomsValue.Value, "CustomsValue").
=Fields!MessageText.Value & Sum(Fields!CustomsValue.Value, "CustomsValue")
Can any advise how I can achieve this?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

2Tall SSChampion Points: 14644 More actions · Answer 1

I have now modified the query as follows:

'£' + CONVERT(varchar(12),CVCustomsValue.CustomFieldValueDecimal,1)

How do I get it to display to 2 decimal places?

I tried:

'£' + CONVERT(varchar(12),CONVERT(decimal(5,2),CVCustomsValue.CustomFieldValueDecimal))

That returned error: Arithmetic overflow error converting numeric to data type numeric (woops!)

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

HI,

Are you sure you VARCHAR value is a correct numeric value to be converted to a decimal?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

2Tall SSChampion Points: 14644 More actions · Answer 3

Hi Christopher. I do not know why I tried that as the data type is decimal so no conversion required!

I simply want to format to 2 decimal places.

'£' + CONVERT(varchar(12),CVCustomsValue.CustomFieldValueDecimal

The above adds the symbol but the output is £10000.00000. I have been asked to remove the .00000 and replace with .00 (or even no decimal places)

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 4

Update

The following workin when I execute the SQL query but returns an error when I run the report?

'£' + CONVERT(varchar(12), CAST (CVCustomsValue.CustomFieldValueDecimal as money),1) AS CustomsValue

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 5

Resolved. My last post resolved the issue, I just needed to modify the report!

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper