Cannot get format to work

nod · Joined: 09 Oct 2007 Posts: 10

I have a variable called 'cost'
It is a number rounded up to 2 decimal places.

I need to display it in a Rich Text box using a comma separator for
thousands and always displaying two digits to the right of the decimal
point.
To do so I have tried numerous permutations declaring cost both as
Currency and Double along with:
FormatCurrency( cost,2)
Format( cost,"#,##0.00")
Format|(cost,"Fixed")
Format|(cost,"Standard")

Nothing works for me.
I would be grateful ifsomeone could tell me what I am doing wrong.

Nod

Archived from group: microsoft>public>vb>syntax

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

>I have a variable called 'cost'
> It is a number rounded up to 2 decimal places.
>
> I need to display it in a Rich Text box using a comma separator for
> thousands and always displaying two digits to the right of the decimal
> point.
> To do so I have tried numerous permutations declaring cost both as
> Currency and Double along with:
> FormatCurrency( cost,2)
> Format( cost,"#,##0.00")
> Format|(cost,"Fixed")
> Format|(cost,"Standard")
>
> Nothing works for me.
> I would be grateful ifsomeone could tell me what I am doing wrong.

Two of the above should work fine (in the classic versions of VB)...
FormatCurrency(Cost,2) should do what you want with a currency symbol
attached whereas Format(Cost,"#,##0.00") should do what you want without the
currency symbol.

Rick

MikeD · Joined: 04 Oct 2007 Posts: 3348

"nod" wrote in message @4ax.com...
>I have a variable called 'cost'
> It is a number rounded up to 2 decimal places.
>
> I need to display it in a Rich Text box using a comma separator for
> thousands and always displaying two digits to the right of the decimal
> point.
> To do so I have tried numerous permutations declaring cost both as
> Currency and Double along with:
> FormatCurrency( cost,2)
> Format( cost,"#,##0.00")
> Format|(cost,"Fixed")
> Format|(cost,"Standard")
>
> Nothing works for me.
> I would be grateful ifsomeone could tell me what I am doing wrong.
>

You should really explain "Nothing works". What result(s) ARE you getting?
Is it just the format that's not right or is the value off by a small amount
(probably only by .01)?

FormatCurrency is locale-aware. So, if your regional settings are set to
use something other than a comma for the thousands separator, that's what
you'll get. The same goes for the currency symbol and the decimal symbol.
But, using Format and a user-defined format string (your second example)
should be using a comma for the thousands separator regardless of regional
settings. Perhaps that will shed some light if the problem is just the
formatting.

If the value itself is off, then it might be due to Format (or
FormatCurrency) itself doing some rounding. Pay close attention to the
ACTUAL value that you're formatting because it might have 3 or more decimal
places (and since you're formatting for 2 decimal places, it could be
getting rounded again).

If you still can't get this sorted out, post back but include the ACTUAL AND
COMPLETE value you're attempting to format.

--
Mike
Microsoft MVP Visual Basic

nod · Joined: 09 Oct 2007 Posts: 10

On Sun, 28 Oct 2007 18:42:43 -0400, "MikeD"
wrote:

>
>"nod" wrote in message
>@4ax.com...
>>I have a variable called 'cost'
>> It is a number rounded up to 2 decimal places.
>>
>> I need to display it in a Rich Text box using a comma separator for
>> thousands and always displaying two digits to the right of the decimal
>> point.
>> To do so I have tried numerous permutations declaring cost both as
>> Currency and Double along with:
>> FormatCurrency( cost,2)
>> Format( cost,"#,##0.00")
>> Format|(cost,"Fixed")
>> Format|(cost,"Standard")
>>
>> Nothing works for me.
>> I would be grateful ifsomeone could tell me what I am doing wrong.
>>
>
>
>You should really explain "Nothing works". What result(s) ARE you getting?
>Is it just the format that's not right or is the value off by a small amount
>(probably only by .01)?
>
>FormatCurrency is locale-aware. So, if your regional settings are set to
>use something other than a comma for the thousands separator, that's what
>you'll get. The same goes for the currency symbol and the decimal symbol.
>But, using Format and a user-defined format string (your second example)
>should be using a comma for the thousands separator regardless of regional
>settings. Perhaps that will shed some light if the problem is just the
>formatting.
>
>If the value itself is off, then it might be due to Format (or
>FormatCurrency) itself doing some rounding. Pay close attention to the
>ACTUAL value that you're formatting because it might have 3 or more decimal
>places (and since you're formatting for 2 decimal places, it could be
>getting rounded again).
>
>If you still can't get this sorted out, post back but include the ACTUAL AND
>COMPLETE value you're attempting to format.

Thank you both for your responses.
I apologise for not giving more specific results.
It was reassuring to learn that Format(cost,"#,##0.00") should have
worked.

I removed the preliminary rounding up.
That had no effect.
The problem lay only in the format, not the value.

cost = 0
cost = Format(cost,"#,##0.00")
displayed as 0
rather than 0.00

cost = 2345.8
cost = Format(cost,"#,##0.00")
displayed as 2345.8
rather than 2,345.80

I then tried something I should have tried earlier.
dim cost as Currency
dim newVar ' (variant)
cost = 2345.8
cost = Format(cost,"#,##0.00")
newVar = Format(cost,"#,##0.00")

cost displayed as 2345.8
newVar displayed as 2,345.80

This has solved the problem for me but I cannot work out why.

dim cost as Currency
dim newVar as Currency
cost = 2345.8
cost = Format(cost,"#,##0.00")
newVar = Format(cost,"#,##0.00")

cost displayed as 2345.8
newVar displayed as 2345.8

MikeD · Joined: 04 Oct 2007 Posts: 3348

"nod" wrote in message @4ax.com...
> On Sun, 28 Oct 2007 18:42:43 -0400, "MikeD"
> wrote:
>
>>
>>"nod" wrote in message
>>@4ax.com...
>>>I have a variable called 'cost'
>>> It is a number rounded up to 2 decimal places.
>>>
>>> I need to display it in a Rich Text box using a comma separator for
>>> thousands and always displaying two digits to the right of the decimal
>>> point.
>>> To do so I have tried numerous permutations declaring cost both as
>>> Currency and Double along with:
>>> FormatCurrency( cost,2)
>>> Format( cost,"#,##0.00")
>>> Format|(cost,"Fixed")
>>> Format|(cost,"Standard")
>>>
>>> Nothing works for me.
>>> I would be grateful ifsomeone could tell me what I am doing wrong.
>>>
>>
>>
>>You should really explain "Nothing works". What result(s) ARE you getting?
>>Is it just the format that's not right or is the value off by a small
>>amount
>>(probably only by .01)?
>>
>>FormatCurrency is locale-aware. So, if your regional settings are set to
>>use something other than a comma for the thousands separator, that's what
>>you'll get. The same goes for the currency symbol and the decimal symbol.
>>But, using Format and a user-defined format string (your second example)
>>should be using a comma for the thousands separator regardless of regional
>>settings. Perhaps that will shed some light if the problem is just the
>>formatting.
>>
>>If the value itself is off, then it might be due to Format (or
>>FormatCurrency) itself doing some rounding. Pay close attention to the
>>ACTUAL value that you're formatting because it might have 3 or more
>>decimal
>>places (and since you're formatting for 2 decimal places, it could be
>>getting rounded again).
>>
>>If you still can't get this sorted out, post back but include the ACTUAL
>>AND
>>COMPLETE value you're attempting to format.
>
>
> Thank you both for your responses.
> I apologise for not giving more specific results.
> It was reassuring to learn that Format(cost,"#,##0.00") should have
> worked.
>
> I removed the preliminary rounding up.
> That had no effect.
> The problem lay only in the format, not the value.
>
> cost = 0
> cost = Format(cost,"#,##0.00")
> displayed as 0
> rather than 0.00
>
> cost = 2345.8
> cost = Format(cost,"#,##0.00")
> displayed as 2345.8
> rather than 2,345.80
>
> I then tried something I should have tried earlier.
> dim cost as Currency
> dim newVar ' (variant)
> cost = 2345.8
> cost = Format(cost,"#,##0.00")
> newVar = Format(cost,"#,##0.00")
>
> cost displayed as 2345.8
> newVar displayed as 2,345.80
>
> This has solved the problem for me but I cannot work out why.
>
> dim cost as Currency
> dim newVar as Currency
> cost = 2345.8
> cost = Format(cost,"#,##0.00")
> newVar = Format(cost,"#,##0.00")
>
> cost displayed as 2345.8
> newVar displayed as 2345.8

Because you are assigning the return value of the Format function right back
to your cost variable (which was declared as Currency).

--
Mike
Microsoft MVP Visual Basic

MikeD · Joined: 04 Oct 2007 Posts: 3348

"MikeD" wrote in message @TK2MSFTNGP02.phx.gbl...
>
> "nod" wrote in message
> @4ax.com...
>> On Sun, 28 Oct 2007 18:42:43 -0400, "MikeD"
>> wrote:
>>
>>
>> cost = 2345.8
>> cost = Format(cost,"#,##0.00")
>> displayed as 2345.8
>> rather than 2,345.80
>>
>> I then tried something I should have tried earlier.
>> dim cost as Currency
>> dim newVar ' (variant)
>> cost = 2345.8
>> cost = Format(cost,"#,##0.00")
>> newVar = Format(cost,"#,##0.00")
>>
>> cost displayed as 2345.8
>> newVar displayed as 2,345.80
>>
>> This has solved the problem for me but I cannot work out why.
>>
>> dim cost as Currency
>> dim newVar as Currency
>> cost = 2345.8
>> cost = Format(cost,"#,##0.00")
>> newVar = Format(cost,"#,##0.00")
>>
>> cost displayed as 2345.8
>> newVar displayed as 2345.8
>
>
> Because you are assigning the return value of the Format function right
> back to your cost variable (which was declared as Currency).

Posted too soon....I meant to add....

And now you know why posting details and actual code is helpful. If you had
done that in your original message, we'd have known that you're using the
Format function to accomplish nothing.

You don't even need the newVar variable. Just format the cost variable when
your actually adding that value to your RTB (or building the string that
you're assigning to the RTB).

--
Mike
Microsoft MVP Visual Basic

nod · Joined: 09 Oct 2007 Posts: 10

On Sun, 28 Oct 2007 20:05:20 -0400, "MikeD"
wrote:

>
snip
>
>Because you are assigning the return value of the Format function right back
>to your cost variable (which was declared as Currency).

Thanks Mike
Yet again I made assumptions about Currency which I should not have.

nod · Joined: 09 Oct 2007 Posts: 10

On Sun, 28 Oct 2007 20:16:39 -0400, "MikeD"
wrote:

>
>"MikeD" wrote in message
>@TK2MSFTNGP02.phx.gbl...
>>
>> "nod" wrote in message
>> @4ax.com...
>>> On Sun, 28 Oct 2007 18:42:43 -0400, "MikeD"
>>> wrote:
>>>
>>>
>>> cost = 2345.8
>>> cost = Format(cost,"#,##0.00")
>>> displayed as 2345.8
>>> rather than 2,345.80
>>>
>>> I then tried something I should have tried earlier.
>>> dim cost as Currency
>>> dim newVar ' (variant)
>>> cost = 2345.8
>>> cost = Format(cost,"#,##0.00")
>>> newVar = Format(cost,"#,##0.00")
>>>
>>> cost displayed as 2345.8
>>> newVar displayed as 2,345.80
>>>
>>> This has solved the problem for me but I cannot work out why.
>>>
>>> dim cost as Currency
>>> dim newVar as Currency
>>> cost = 2345.8
>>> cost = Format(cost,"#,##0.00")
>>> newVar = Format(cost,"#,##0.00")
>>>
>>> cost displayed as 2345.8
>>> newVar displayed as 2345.8
>>
>>
>> Because you are assigning the return value of the Format function right
>> back to your cost variable (which was declared as Currency).
>
>
>Posted too soon....I meant to add....
>
>And now you know why posting details and actual code is helpful. If you had
>done that in your original message, we'd have known that you're using the
>Format function to accomplish nothing.
>
>You don't even need the newVar variable. Just format the cost variable when
>your actually adding that value to your RTB (or building the string that
>you're assigning to the RTB).

Thanks again - I take your point.

The actual code does involve formatting cost as the string is built
and that is why I presented it as I did - to make it more clear I had
hoped.

newVar was simply used to help identify the problem once I knew the
Format function was correct. It was never intended for actual use.