×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Convert VBA to SQL

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

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:

CODE -->

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

Into this sort of text:

CODE -->

"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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources