Results 1 to 3 of 3
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    Segment a string

    using some code



    Code:
        Dim strSQL As String
        'Purpose:   Convert a SQL statement into a string to paste into VBA code.
        Const strcLineEnd = " "" & vbCrLf & _" & vbCrLf & """"
        
        If IsNull(Me.txtSql) Then
            Beep
        Else
            strSQL = Me.txtSql
            strSQL = Replace(strSQL, """", """""")  'Double up any quotes.
            strSQL = Replace(strSQL, vbCrLf, strcLineEnd)
            strSQL = "strSql = """ & strSQL & """"
            Me.txtVBA = strSQL
            Me.txtVBA.SetFocus
            
        End If
    This moves sql into a string for vba however

    every so many characters (say 200) I would like to add another end string with " & _ and then vbCrLf and add another & " to that next line

    That way everything fits nicely on my page.

    any suggestions?

    I was thinking of using left() and then inserting it all somehow at that position - not sure how

  2. #2
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    India
    Posts
    616
    This can be done using the mid() function but it will break the string at unwanted places like tblEmployees may break into tb on first line and lEmployees on second line.
    Divide the length of string in desired parts and loop through it replacing the desired string at determined intervals.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by amrut View Post
    This can be done using the mid() function but it will break the string at unwanted places like tblEmployees may break into tb on first line and lEmployees on second line.
    Divide the length of string in desired parts and loop through it replacing the desired string at determined intervals.
    I guess I could look for things like "," and space the mid every 6 or so with a replace(mid()) although counting the , in a string might be the next challenge.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. String
    By Rhubie in forum Queries
    Replies: 1
    Last Post: 05-28-2013, 09:15 PM
  2. Vba string
    By rchtan in forum Programming
    Replies: 5
    Last Post: 05-17-2012, 04:29 AM
  3. SQL + VBA String
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 08-22-2011, 08:44 AM
  4. VBA string with OR expression
    By hmcquade in forum Forms
    Replies: 3
    Last Post: 06-24-2011, 07:59 AM
  5. String Matching
    By tuna in forum Programming
    Replies: 1
    Last Post: 05-16-2010, 12:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums