Convert VBA to SQL

[vba317]

I am using access 2003. I am trying to write a function to change vba copied from a query into sql. I am using the replace function with limited results. I have been able to add quotes to the beginning and end of my statement. Since I am using the SQL to populate record sets I would like specific outputs on my statements. The method I am using is copying and pasting the original code into a text box. I have put my converting code behind a button on a form. So My goal if possible is to click on the button and have my code converted. My first issue Since I can't figure out how to select the correct point to add a carriage return. Currently It adds an " & _" after each comma is it possible to get the replace function to replace after every second comma? Any help is appreciated. The name of my original textbox is txtOrig. The conversion textbox is called txtReformatted.

Private Sub cmdConvert_Click()
    Dim strSql As String
    Dim strTblName As String
    Dim strRpl As String
    Dim iBegCnt As Integer
    Dim iEndCnt As Integer
    Dim X As Integer
    'Purpose:   Convert a SQL statement into a string to paste into VBA code.
    Const strcLineEnd = "  "" & _" & vbCrLf & """"
    Const strAddCr = " "" """
    If IsNull(Me.txtOrig) Then
       
    Else
        strSql = Me.txtOrig
        strSql = Replace(strSql, ",", vbCrLf)
        strSql = Replace(strSql, vbCrLf, strcLineEnd)
               
        For X = 1 To 13
        If X = 1 Then strRpl = "SumofJan"
        If X = 2 Then strRpl = "SumofFeb"
        If X = 3 Then strRpl = "SumofMar"
        If X = 4 Then strRpl = "SumofApr"
        If X = 5 Then strRpl = "SumofMay"
        If X = 6 Then strRpl = "SumofJun"
        If X = 7 Then strRpl = "SumofJul"
        If X = 8 Then strRpl = "SumofAug"
        If X = 9 Then strRpl = "SumofSep"
        If X = 10 Then strRpl = "SumofOct"
        If X = 11 Then strRpl = "SumofNov"
        If X = 12 Then strRpl = "SumofDec"
        If X = 13 Then strRpl = "SumOfTotal"
        Debug.Print X
        'if endCnt=2 then strSql=
        strSql = Replace(strSql, strRpl, X)
        Next
        Me.txtReformatted = strSql
        Me.txtReformatted.SetFocus
        RunCommand acCmdCopy
    End If
End Sub

 

[LiteYear]

Took me a while to figure out firstly what you were doing and secondly what the problem was. Is this right:

You want to turn this sort of text:

SELECT * FROM tblMain.f1, tblMain.f2, tblMain.f3
ORDER BY [f1]

Into this sort of text:

"SELECT * FROM tblMain.f1, tblMain.f2," & _
"tblMain.f3"
"ORDER BY [TableID]"

in VBA?

And you're stuck because Replace is replacing the comma as you wish, but is doing so for the first comma it comes to and you want it to only work on the second comma?

If that's the case then as far as I know, Replace doesn't have the functionality you're after. You could try to hobble something together with InStr to locate the first comma and then passing that to the optional "start" parameter of Replace to skip the first comma. But because Replace only returns the characters it searches, I think it gets messy quickly.

I'm no expert, but I'd be looking instead at the Split command - split your string on all commas first, then put them back into a single string in whatever manner you choose.

HTH