How to convert a comma into a decimal point for input in an SQL string

[MickelR]

I have made a listbox, which I maintain using an SQL string. Problem is that in The Netherlands most international settings on computers have dots as thousand separator and comma's as decimal separator (i.e. $1.000,00)

I would want to convert that number to the american standard, because american standards is all that SQL statements accept. Converting a date is easy, using the VBA format function (i.e. datAnyDate = format([date],"mm/dd/yyyy"). However, I can't get it right with numbers. This is what I tried:

Dim strValue as string
Dim dblValue as double

strValue = Replace(Me.cboSelection1, ",", ".")
dblValue = Val(strValue)

me.listbox.recordset = ("SELECT * FROM tblYYY WHERE tblField = " & dblValue)

tblField in this example has the property number with field size double.

The result of strValue is a value with a dot in it as decimal separator. I expected the replace function to do this, so this works right. The result of dblValue in this code is still the value including a comma as separator (as it is set in the international settings of the computer). It doesn't surprise me that the Val function converts the number with a comma as decimal separator again. Obviously it is not possible to use strValue in the SQL statement. After all, it's a string, so using this in an SQL statement requires the use of quotes.

I really need this piece of code to work independently from the locale international settings of the computer. Who is smart enough to make the SQL statement work independently from the international settings of the computer?

 

[PHV]

Have you tried this ?

Me.listbox.RowSource = "SELECT * FROM tblYYY WHERE tblField=" & Replace(Me!cboSelection1, ",", ".")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MickelR]

I didn't try that! The solution can be so obvious at times. Thank you for this paradigm and opening my eyes. You solved my mystery.

 

[Andrzejek]

It also depends on what you have to start with.
If this is what you have:

Debug.Print Me.cboSelection1.Text
$1.000,00[green]   '(i.e. $1.000,00)[/green]

Then you mat think about 2 steps:
1. Get rid of a period - you end up with $1000,00
2. Replace a comma with a dot - you end up with $1000.00

Me.listbox.RowSource = "SELECT * FROM tblYYY WHERE tblField = " & [blue]Replace([/blue]Replace(Me!cboSelection1, ".", "")[blue],",", ".")[/blue]

Have fun.

---- Andy

 

[TheAceMan1]

How are ya MickelR . . .

Bear in mind when we use seperators, were talking a formatted view ... aka a string. The problem is when we change the decimal seperator from a comma to a period, were left with all periods 1.000.000,25 becomes 1.000.000.25! To fix this:
[ol][li]We initially change the comma to a character other than a period ... say "@".[/li]
[li]Change any remaining periods to a comma.[/li]
[li]Finally change "@" to a period. This all works out to be:

[blue]Dim dblValue As Double
dblValue = [purple][b]Replace(Replace(Replace(Me.cboSelection1, ",", "@"), ".", ","), "@", ".")[/b][/purple]

Me.ListBox.Recordset = ("SELECT * FROM tblYYY WHERE tblField = " & dblValue)[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MickelR]

I appreciate all your input and thinking with me on this. Your suggestions helped me closing this gap that gave me a huge headache initially. I could not find the solution to my request anywhere, either, so I hope that after the search engines indexed this page, this question will help other, as well.

 

[Gruuuu]

Also, MickelR, If you valued anyone's posts in particular, you can show your appreciation by clicking on the Purple Star beneath their post

   [u][b][purple]*[/purple]Thank So-and-So
for this valuable post![/b][/u]