VBA Project, change of a decimal separator in a variable value

Question

I'm trying to upload a value directly from VBA to SQL via ADO - I collect values into a Dim srtSql As String and then insert them into the SQL table. The issue relates to a "Currency" value with a comma delimiter. I have a comma decimal separator and the value of my variable looks like (94,5), but a dot (94.5) is needed. The code below doesn't solves the problem.

With Application.
    .DecimalSeparator = "."
    .UseSystemSeparators = False
End With

While searching for a solution I've got a code below from the case How to ensure dot as decimal separator when exporting excel to csv? to check the separator.

Dim myShell, regDecSep    
Set myShell = CreateObject("WScript.Shell")
regDecSep = myShell.RegRead("HKCU\Control Panel\International\sDecimal")

Debug.Print "System decimal separator: " & regDecSep

I'm not familiar with Registry. My question - is it any way to set a system decimal separator via RegWrite or any other method? P.S. I would like to avoid a vba Replace() solution.

Your code has a trailing dot. That won't work. Did you run the full code from the answer linked? — Gustav
– Gustav, Commented Jul 21, 2022 at 9:10
The link points to a question that exports data into CSV, but you are talking about SQL. Do you export the data into CSV and read that file from your database or do you write the data directly from VBA? — FunThomas
– FunThomas, Commented Jul 21, 2022 at 9:16
@Gustav thank you for your comment, What do you mean by a " trailing dot"? — Mik
– Mik, Commented Jul 21, 2022 at 9:19
@FunThomas I collect values into a Dim srtSql As String and then insert them via ADO into the SQL table. I have a "Currency" value with a comma delimiter. — Mik
– Mik, Commented Jul 21, 2022 at 9:27

Gustav · Accepted Answer · 2022-07-21 10:53:48Z

1

Your code is about Excel, not SQL. So, convert decimal values to string expressions for these having a dot as the decimal separator. Str will do that:

strSql = "Insert Into Table1 (SomeTextField, Amount, AnotherTextField) " & _
    "Values ('" & Text1 & "', Str(" & YourAmount & "), '" Text2 & "')"

Also, see my function CSql for concatenating SQL expressions.

answered Jul 21, 2022 at 10:53

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mik Over a year ago

Thank you so much !!! For the answer and for the function. It's so helpfull. I already copied it into MyLib ). Million Thanks!

Collectives™ on Stack Overflow

VBA Project, change of a decimal separator in a variable value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related