displaying commas and two decimal places in a textbox or label

[kimangel]

hi experts,

i have problem in displaying commas and decimal to a certain amount in a textbox and label. it only display certain amount without comma.

below are my codes;

______
Private Sub lblAllowable_Change()
lblAllowable.Caption = Format("4321", "#,##0.00")
End Sub
____

____
Private Sub cmdSearch_Click()

txtLastname.Text = ""
txtFirstname.Text = ""
txtMiddle_Initial.Text = ""

Set rs = New ADODB.Recordset
rs.Open "Select Lastname, Firstname, Middle_Initial, Rate from Employees where Employees_IdNo Like '" & txtSearch & "'", cn, adOpenKeyset, adLockPessimistic
If Not rs.EOF Then

lblAllowable.Caption = Round(Val(lblRate * 30 * 0.4), 2)

Ado.RecordSource = "Select Lastname, Firstname, Middle_Initial, Rate from Employees where Employees_IdNo Like '" & txtSearch & "'"
Ado.Refresh

rs.Close
Set rs = Nothing

Else
MsgBox "No entry yet for this ID Number", vbCritical, "Charges"
End If
End Sub
____

[tinstaafl]

try lblAllowable.Caption = Format(Round(Val(lblRate * 30 * 0.4), 2), "#,##0.00")

[AndreRet]

I normally like to break things down into steps, better to keep track if things start going wrong...

If Not rs.EOF Then

lblAllowable.Caption = Round(Val(lblRate * 30 * 0.4), 2)

lblAllowable.Caption = Format(lblAllowable.Caption, "#,##0.00")

Ado.RecordSource = "Select Lastname, Firstname, Middle_Initial, Rate from Employees where Employees_IdNo Like '" & txtSearch & "'"
Ado.Refresh

[kimangel]

thank you sir for this, the label output format is already solved but on the textbox I still have little problems. When I type a number on the textbox, the number will not automatically put a comma and dot for decimal places, how would I code this sir? Once again, Thank you.

[AndreRet]

Add the code in your textbox LostFocus event. In other words, all the data gets added. Once the textbox loose focus, it will format the contents...

[tinstaafl]

You could also use the Masked Edit Control. Set the format to "#,##0.00", then every time the textbox loses focus the textbox gets formatted. by setting the mask to "####.##", you can restrict the input to only digits and 2 decimal places.

[kimangel]

thank you sir, having comma and dot in a textbox is now working, but when I add the three textboxes, namely txtCash_Advance, txtCash_Advance2, and txtCash_Advance3, displayed on the label, there are error on adding, only hundreds will be added correctly but with 4 digit numbers, the 4th digit will be recomputed/added as 1, example when I type 1,000.00 on the 3 textboxes the display is 3 only.

Sir, pls help me check my code below. Thanks alot to both of you.

Private Sub lblTotal_CashAdvances_Change()
lblTotal_Charges.Caption = Val(lblTotal_CashAdvances) + Val(lblTotal_SunLoadCharges) + Val(lblTotal_OtherCharges)
End Sub

Private Sub txtCash_Advance_Change()
lblTotal_CashAdvances.Caption = Round(Val(txtCash_Advance) + Val(txtCash_Advance2) + Val(txtCash_Advance3), 2)
End Sub

Private Sub txtCash_Advance_LostFocus()
txtCash_Advance.Text = Format(txtCash_Advance.Text, "###,##0.00")
End Sub

Private Sub txtCash_Advance2_Change()
lblTotal_CashAdvances.Caption = Round(Val(txtCash_Advance) + Val(txtCash_Advance2) + Val(txtCash_Advance3), 2)
End Sub

Private Sub txtCash_Advance2_LostFocus()
txtCash_Advance2.Text = Format(txtCash_Advance2.Text, "###,##0.00")
End Sub

Private Sub txtCash_Advance3_Change()
lblTotal_CashAdvances.Caption = Round(Val(txtCash_Advance) + Val(txtCash_Advance2) + Val(txtCash_Advance3), 2)
End Sub

Private Sub txtCash_Advance3_LostFocus()
txtCash_Advance3.Text = Format(txtCash_Advance3.Text, "###,##0.00")
End Sub

[AndreRet]

You need to start using variables, much easier to work with. :)

Your code, scrap it and change to the following...

''Remove the change event. That is where your calculations gets messed up because it keeps on calculation changeable values...
''Private Sub txtCash_Advance_Change()
''lblTotal_CashAdvances.Caption = Round(Val(txtCash_Advance) + Val(txtCash_Advance2) + Val(txtCash_Advance3), 2)
''End Sub

Private Sub txtCash_Advance_LostFocus()
txtCash_Advance.Text = Format(txtCash_Advance.Text, "###,##0.00")

Dim x1 As Integer, x2 As Integer, x3 As Integer, xTotal As Integer

x1 = Round(txtCash_Advance.Text, 2)
x2 = Round(txtCash_Advance2.Text, 2)
x3 = Round(txtCash_Advance3.Text, 2)

xTotal = Val(x1) + Val(x2) + Val(x3)

lblTotal_CashAdvances.Caption = xTotal
End Sub

[kimangel]

sir thanks those codes really helps, but on the label total, lblTotal_CashAdvances, the centavo were roundoff to the nearest whole number, e.g 1.45, were roundoff to 1 which not supposed to be. I tried to add this "lblTotal_CashAdvances.Caption = Format(lblTotal_CashAdvances.Caption, "###,##0.00")" and remove the word "round" in the format but still the same. How would display the centavos as is?

[AndreRet]

Just change Round to Format...

x1 = Format(txtCash_Advance.Text, "##,##0.00)
x2 = Format(txtCash_Advance2.Text, "##,##0.00)
x3 = Format(txtCash_Advance3.Text, "##,##0.00)

That should do the trick

[kimangel]

thank you sir, i forgot that we used Interger which i supposed to be Double for value having decimal value. Thank you once again.

[AndreRet]

I should have caught that. :) Pleasure and happy coding.