Thousand seperater

[PM312]

hi

I am trying to add numeric text with thousand seperater format in three textboxes.
Problem is amount after thousand seperate get ignored i.e if text1.text is 1,250.00 text2.text is 3,500.00 and text3.text is 2,100.00 the result in text4.text is 6.00 instead of 6,850.00

Text4.Text = Text1.Text + Text2.Text + Text3.Text

Text4.Text = 1,250.00 + 3,500.00 + 2,100.00 result is 6.00 instead of 6,850.00

if i remove thousand seperater format result is ok.ie 6850.00

can anyone advise what is the problem . i want to use thousand seperater.

thanks

[vb5prgrmr]

Try...

Text4.Text = Format(CDbl(Text1.Text) + CDbl(Text2.Text) + CDbl(Text3.Text), "#,#.00")

Good Luck

[AndreRet]

NOW, before vb5 is on my back and tell me how ashamed I should be... hahahaha, his code is correct, but I have received errors previously with his solution.

I normally use the following which has never genereated errors before, assuming that the text entered is numerical -

Text4.Text = Val(Text1.Text) + Val(Text2.Text) + Val(Text3.Text)
Text4.Text = Format(Text4.Text, "#,#.00")
'The .00 will round your calculation to 2 decimals only.

[vb5prgrmr]

Andre, if you look at our codes... they are almost the same :) Just mine does it with one line...

Now lets talk about the differences between your code and mine...

First up, the help file information...

Val Function

Returns the numbers contained in a string as a numeric value of appropriate type.

Syntax

Val(string)

The required string argument is any valid string expression.

Remarks

The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized. However, the function recognizes the radix prefixes &O (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are stripped from the argument.

The following returns the value 1615198:

Val(" 1615 198th Street N.E.")

In the code below, Val returns the decimal value -1 for the hexadecimal value shown:

Val("&HFFFF")

Note The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.

So what does this mean???

It means that the OP's values of 1,250.00 + 3,500.00 + 2,100.00 would result in an answer of 6.00 instead of 6,850.00 but lets check to make sure...

Standard exe project, add 4 text boxes (Text1-Text4)... add code...

Option Explicit

Private Sub Form_Load()
Text1.Move 30, 30, 1500, 315
Text2.Move 1560, 30, 1500, 315
Text3.Move 3090, 30, 1500, 315
Text1.Text = "1,250.00"
Text2.Text = "3,500.00"
Text3.Text = "2,100.00"

'result is 6.00 instead of 6,850.00"
Text4.Text = Val(Text1.Text) + Val(Text2.Text) + Val(Text3.Text)
Text4.Text = Format(Text4.Text, "#,#.00")

'msgbox is correct...
MsgBox Format(CDbl(Text1.Text) + CDbl(Text2.Text) + CDbl(Text3.Text), "#,#.00")

End Sub

Now this is because Val stops reading at the comma character while CDbl does not...

Good Luck

[AndreRet]

As always you are soooo correct.:$

AGAIN shame on me, I did not take the commas or other characters in consideration because I normally control what gets added into a textbox. DAMN!!!

Seems like I owe you now!:P

[PM312]

Hi,

Thanks for your reply

I tried below code & it's works ok.

Text4.Text = Format(CDbl(Text1.Text) + CDbl(Text2.Text) + CDbl(Text3.Text), "#,#.00")

But gives mismatch error when text is empty. so i tried below code & it's work ok for empty text also.

Text4.Text = Format(CDbl(Val(Text1.Text)) + CDbl(val(Text2.Text)) + CDbl(Val(Text3.Text)), "#,#.00")

But above does not work when i tried to sum coloum in flex grid.

Dim TOTAL As Double

TOTAL = 0

Dim GrdRNO As Integer ' Flexgrid row no

For GrdRNO = 1 To FGD.Rows - 1  ''FGD -flexgrid name

TOTAL = TOTAL + CDbl(Val(FGD.TextMatrix(GrdRNO, 6))) '' COLOUM 6 IS AMOUNT COLOUM WITH THOUSAND SEPERATER FORMAT


Next GrdRNO

TotalAmt.Text = Format(TOTAL, "#,##0.00") '' TotalAmt is textbox where i Display total of amount coloum where the amount is not display correctly.

the result should have been same as in case of text boxes. please suggest where is the error
Thanks

Pravin

[vb5prgrmr]

Well you need to error check for empty text boxes and if = to vbnullstring then textbox.text = "0.00"...

I forget, is column zero based or 1 based???

Good Luck

[AndreRet]

Zero based,

Add the Cdbl to your Total as well -

Total = Cdbl(Val(Total) + CDbl(Val(FGD.TextMatrix(GrdRNO, 6)))

This should sum the column fields, I think.

[PM312]

Hi

it still not working. Attached sample code project.

Thanks
pravin

[AndreRet]

Use the following code, it works 100%, mostly thanks to vb5....

Option Explicit

Private Sub DoSum(FGD As MSFlexGrid)

Dim TotRows As Integer
Dim RowAmount As String, Total As String

Total = 0

For TotRows = 1 To FGD.Rows - 1
    RowAmount = FGD.TextMatrix(TotRows, 6)
    Total = CDbl(Total) + CDbl(RowAmount)
Next TotRows

TotalAmt.Text = Total
End Sub

Private Sub Form_Load()

FGD.FormatString = "^S.No| Item Discription         |Item Code  |^UOM|^ Quantity|> Rate |> Amount"
Me.Height = 10000
Me.Width = 11000
Me.Top = 10
Me.Left = 1000
FGD.ColWidth(0) = 800
FGD.ColWidth(1) = 3000
FGD.ColWidth(2) = 1000
FGD.ColWidth(3) = 1000
FGD.ColWidth(4) = 1000
FGD.ColWidth(5) = 1000
FGD.ColWidth(6) = 1500

FGD.TextMatrix(1, 0) = 1
FGD.TextMatrix(1, 1) = "Sugar"
FGD.TextMatrix(1, 2) = ""
FGD.TextMatrix(1, 3) = "Kgs"
FGD.TextMatrix(1, 4) = 100
FGD.TextMatrix(1, 5) = 32
FGD.TextMatrix(1, 6) = Format(FGD.TextMatrix(1, 4) * FGD.TextMatrix(1, 5), "#,##0.00")

FGD.TextMatrix(2, 0) = 2
FGD.TextMatrix(2, 1) = "Rice"
FGD.TextMatrix(2, 2) = ""
FGD.TextMatrix(2, 3) = "Kgs"
FGD.TextMatrix(2, 4) = 50
FGD.TextMatrix(2, 5) = 16
FGD.TextMatrix(2, 6) = Format(FGD.TextMatrix(2, 4) * FGD.TextMatrix(2, 5), "#,##0.00")

FGD.TextMatrix(3, 0) = 3
FGD.TextMatrix(3, 1) = "Wheat"
FGD.TextMatrix(3, 2) = ""
FGD.TextMatrix(3, 3) = "Kgs"
FGD.TextMatrix(3, 4) = 150
FGD.TextMatrix(3, 5) = 42
FGD.TextMatrix(3, 6) = Format(FGD.TextMatrix(3, 4) * FGD.TextMatrix(3, 5), "#,##0.00")

DoSum FGD
End Sub

[PM312]

Thanks

[AndreRet]

Only a pleasure.