Convert VBA to SQL
Convert VBA to SQL
(OP)
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.
CODE
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 




RE: Convert VBA to SQL
You want to turn this sort of text:
CODE -->
Into this sort of text:
CODE -->
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