When i try this, i get different results.

The value in the cell is 502,200.55. Trying to format the cell in
general/value/text has no effect. Result is always the same.

The code in full:

-----------

Dim strPointNum As String, strCommaNum As String, p As Integer
Dim strSQL As String
strCommaNum = Format(ActiveSheet.Cells(8, 5), "0.00")
p = InStr(1, strCommaNum, ",", vbTextCompare)
strPointNum = Left(strCommaNum, p - 1) & "." & Right(strCommaNum, 2)
strSQL = "INSERT INTO company (salary) SELECT " & strPointNum & ";"

-----------

After running all lines, the Watch tells me the variables get values:

p = 4
strCommaNum = "502,200.55"
strPointNum = "502.55"
strSQL = "INSERT INTO company (salary) SELECT 502.55;"

-----------

I don't know how you get value 502200.55. I have nothing else in my code.

BR,
Poppe



"NickH" wrote:

Hi Poppe

I have done some more experimentation on this and my code works for both
cases of your sample data. In my case, I am doing the opposite from you;
i.e. taking numbers from UK locale (123,456.78) to European locale
(123.456,78) format.

I thought that your original data cells might be formatted as text, rather
than numbers. However, running my code on string format numbers still works.
I used "502,200.55" and "502,200.5" as string literals and they came out as
"502200,55" and "502200,50" respectively. The statement
'Format(ActiveSheet.Cells(8, 5),"0.0")' returns "502200.55" and "502200.50"
(in your system, you would have "," instead of "."). In other words, it has
stripped out the thousands separators, leaving just the decimal point. From
this point on, it is simply string manipulation - so providing you don't go
back to a number between deconstruction and the SQL statement, it should work.

Not sure where to go next on this.

--
Nick