none
Convert a string from a TextBox to a number with point as decimal separator

    Question

  • 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...

    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
    
    Thanks, Paolo

    Friday, March 11, 2011 11:48 AM

Answers

  • There 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, ",", ".")

     

    Enjoy,
    Tony
    www.WordArticles.com
    • Marked as answer by gaio78 Friday, March 11, 2011 3:58 PM
    Friday, March 11, 2011 12:33 PM

All replies

  • There 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, ",", ".")

     

    Enjoy,
    Tony
    www.WordArticles.com
    • Marked as answer by gaio78 Friday, March 11, 2011 3:58 PM
    Friday, March 11, 2011 12:33 PM
  • Following 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 2:12 PM
  • Uuuhhh! I'm a chicken! :)

    I wrote

    number = Replace(Textnumber.Value, ".", ",")
    

    but you're right... the correct string is

    number = Replace(Textnumber.Value, ",", ".")
    

    Thanks a lot!!

    Friday, March 11, 2011 3:58 PM