+ Reply to Thread
Results 1 to 3 of 3

Comma decimal separator with SQL insert

  1. #1
    Registered User
    Join Date
    05-12-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    2

    Comma decimal separator with SQL insert

    I am really struggling with this one. I am using a macro to write a SQL insert script and this is being used across the world so I have some users that have , and some that have . as a decimal separator.

    A snippet of the VBA is below. I feel like I can fix this either on the Excel side or the SQL side but I haven't been able to come up with a solution to either.



    Please Login or Register  to view this content.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Comma decimal separator with SQL insert

    Here's concept I use: I write the SQL with "tokens" for what I want to be dynamic and then use SUBSTITUTE to replace it and use a short UDF called SUPERCAT to put it all together.

    In the attached on Sheet 1 in Cells A4:14 is your code with the underbars removed (I will be concatenating it to all one string). I gave a name to cell A1: I called it "Delimiter". Put in your comma or period here. Column B substitutes the delimiter for the comma. Cell A1 is called SQL_String, and has the formula: =SuperCat(B4:B14) - this merely concatenates cells B4:B14 into a big sting.

    Now in your code you can use:
    Please Login or Register  to view this content.
    This is a part of a much bigger program that I use to make dynamic, runtime SQL statements. http://www.utteraccess.com/wiki/index.php/MS_Query.
    Attached Files Attached Files
    Last edited by dflak; 06-07-2016 at 10:48 AM. Reason: Attach file
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    05-12-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Comma decimal separator with SQL insert

    I ended up solving it using a replace check.

    If j = 0 Or j = 1 Or j = 4 Or j = 5 Or j = 8 Or j = 9 Or j = 12 Or j = 14 Or j > 15 Then
    SQLLAG(j) = Replace(SQLLAG(j), ",", ".")
    End If

    I think your method would have worked better as a system and dynamic approach. I have to re-code it all later this is just a template. I'm going t try your way.

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Changing Separator Comma In Excel
    By NeedForExcel in forum Excel General
    Replies: 2
    Last Post: 03-09-2016, 12:35 AM
  2. Replies: 1
    Last Post: 03-28-2013, 12:44 PM
  3. decimal separator is comma, not period
    By lmmohr in forum Excel General
    Replies: 2
    Last Post: 02-13-2012, 02:32 PM
  4. COMMA separator gone in Formulas
    By Gerhard Boshoff in forum Excel General
    Replies: 6
    Last Post: 01-17-2012, 05:49 PM
  5. replace the comma separator to a dot and dot to a comma at the same time
    By barkiny in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2010, 05:42 PM
  6. [SOLVED] thousand separator conversion from dot to comma
    By Spike in forum Excel General
    Replies: 10
    Last Post: 01-31-2006, 05:10 PM
  7. [SOLVED] thousands comma separator in VBA
    By Wendy Francis in forum Excel General
    Replies: 2
    Last Post: 10-24-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1