Friday, March 11, 2011 11:48 AM
I need to pass a number from a TexBox with this format 1,2 to a sql string in this format 1.2
My locale is It-it and uses decimal point with a comma (1,2).
How can I do this change in VBA? I wrote this code...Thanks, Paolo
Private Sub insert_article_Click() 'set decimal separator as point With Application .DecimalSeparator = "." .ThousandsSeparator = "," .UseSystemSeparators = False End With Dim number As Double Dim word As String word = "test"<br/> number = CDbl(Textnumber.Value) 'if I enter 1,2 ,number will be 1,2 'I try also this, but number will be 1,2 'number = Replace(Textnumber.Value, ".", ",") strSQL = "INSERT INTO table (col_word, col_number) VALUES ('" & word & "'," & number & ")" ... End Sub
Friday, March 11, 2011 12:33 PMThere isn't any easy way to do this. Application.DecimalSeparator, etc. affect the Excel UI, not VBA. As long as you dont want, or need, thousands separators, you can use Replace as your commented code shows, but you have the dot and the comma the wrong way round - try ....number = Replace(Textnumber.Value, ",", ".")
- Marked As Answer by gaio78 Friday, March 11, 2011 3:58 PM
Friday, March 11, 2011 2:12 PMFollowing Tony's post, ensure the 'number' variable is declared a string. If it's variant, the replace may not work properly.
Tiago Cardoso VB / VBA Analyst
Friday, March 11, 2011 3:58 PM
Uuuhhh! I'm a chicken! :)
number = Replace(Textnumber.Value, ".", ",")
but you're right... the correct string is
number = Replace(Textnumber.Value, ",", ".")
Thanks a lot!!