Results 1 to 9 of 9
  1. #1
    TDinDC is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Location
    Hyattsville MD
    Posts
    3

    Process Complex Strings

    I have been designing an Access application that analyzes and documents VBA code from Access applications I have built or am working on. After importing the code, it documents all subs and functions, their input and output args, the calls made from within each and the recordsets created. Some recordsets are created with complex SQL statements with embedded quotes, "&", chr$(34) and other characters that build the SQL string.



    The issue I face is how to process these strings to return SQL that removes such characters. Since VBA recognizes them as strings, simple edits do not work, as they can unbalance quotes, et cetera. Here is an example of one such string where my ReplaceString function failed. After, I show the ReplaceString function as it currently is.

    SELECT * FROM tblImport WHERE (([ID] = " & ImportID & ") AND ([FileName] LIKE " & Chr$(34) & "*.enc" & Chr$(34) & "))

    Function ReplaceString(FindStr As String, Str As String, ReplaceWith As String) As String
    Dim s As String, i As Integer, Lf As Integer
    Rem REPLACE EVERY INSTANCE OF FindStr OCCURRING IN Str WITH ReplaceWith
    Rem NOT WORKING
    Lf = Len(FindStr) ' length of the string to find


    Do
    i = InStr(1, Str, FindStr, vbTextCompare) ' where does FindStr occur in Str?
    If i = 0 Then Exit Do
    s = Mid$(Str, 1, i - 1) & ReplaceWith & Mid$(Str, i + Lf) ' Str after replacement
    Loop


    ReplaceString = s ' return value
    End Function

    Several calls would be made to this function. For example:

    s = ReplaceString(chr$(34), s, "") [Remove a quote string]
    s = ReplaceString(" & ", s, "") [Remove the & that concatenates strings]
    and
    s = ReplaceString("Chr$(34)", s, "'") [Replace the string Chr$(34) with a single quote]
    I remember having a similar issue with strings with embedded strings years ago. Any ideas?

    TDinDC

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us your input SQL and the result you are trying to get.

    If you have saved queries you can get the sql directly from the querydef.

    Can you tell us or show us more of your application/utility?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Just saying it failed is not very helpful. What data input caused the failure? Was there an error message, wrong result or nothing happened?

    Please post code within CODE tags to retain indentation and readability.

    Instead of removing apostrophes and quotes, can double them so they are 'escaped' as text, not special characters. I do this for strings like: O'Malley, John's Road.

    Replace(string, "'", "''")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think we need to see the exact function call. From your example, I don't see where you're passing the actual string to be searched (str) to the function to
    If this s = ReplaceString(chr$(34), s, "") [Remove a quote string]
    is the actual call, then ask the function to accept a variable of unknown value ("s") as the string, which happens to be empty (""). You can't pass an unknown or empty string to this function and expect it to return a modified string when there's nothing to start with. It's like a catch 22.

    I think you have too many variables altogether, plus if it does find the character, i will become something other than zero and you will be stuck in an endless loop. That's because the InStr function returns the location of the first instance of the string to be found. You have to increment your starting point.

    P.S. please use code tags around your code and indent as appropriate. The tags make it easier to read (uniform spacing) plus the forum will insert spaces after 50 contiguous characters if you do not, which makes us think there are errors.

    EDIT: no intent to chastise re the code tags. I've been playing with this for a while and June7 answered in the meantime, so I didn't see it. I was also thinking why the Replace function couldn't just be used.
    Last edited by Micron; 07-17-2017 at 07:43 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    TDinDC is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Location
    Hyattsville MD
    Posts
    3

    Wink

    Sorry I didn't know about Code Tags. I'll include them in the re-post.

    This post is about a function ReplaceString. The function ReplaceString is one of many routines in an application I am building to analyze and document Access VBA code in whatever Access application I choose. I have been building Access applications since the early 1990s, and there is occasionally old VBA code that I would like to revise. So, this application is for the purpose of more easily understanding that old code.

    The bolded line below is a SQL example that came from VBA in an old Access application I developed that is to be analyzed:

    SQL = "SELECT * FROM tblImport WHERE (([ID] = " & ImportID & ") AND ([FileName] LIKE " & Chr$(34) & "*.enc" & Chr$(34) & "))"

    There are three separate calls to ReplaceString to fix this SQL string so that it can be stored in a table in a format that is easy to understand. They are:

    (1) SQL = ReplaceString(" & ",SQL, "") which should replace the concatenating " & " between sub-strings of SQL with an empty string,
    (2) SQL = ReplaceString(Chr$(34), SQL, "") which should remove the quotes surrounding sub-strings of SQL, and
    (3) SQL = ReplaceString("Chr(34)", SQL, "'") which should replace the actual occurrences of "Chr$(34) - a quote mark - with single quotes

    The SQL string the function should return is this:

    SQL = SELECT * FROM tblImport WHERE (([ID] = ImportID ) AND ([FileName] LIKE '*.enc')) where ID and FileName​ are variables whose values are ImportID and '*.enc' respectively.

    Here is the code for ReplaceString. Please note well the order of the arguments to the function. They are (1) the substring to be searched for, (2) the complete string to be searched, and (3) the replacement string:

    Code:
    Function ReplaceString(FindStr As String, Str As String, ReplaceWith As String) As StringDim s As String, i As Integer, Lf As Integer
    Rem REPLACE EVERY INSTANCE OF FindStr OCCURRING IN Str WITH ReplaceWith
    
         Lf = Len(FindStr) ' length of the string to find
    
         s = Str
         Do
             i = InStr(1, s, FindStr, vbTextCompare) ' where does FindStr occur in Str?
             If i = 0 Then Exit Do
             s = Mid$(s, 1, i - 1) & ReplaceWith & Mid$(s, i + Lf) ' Str after replacement
         Loop ' loop until all replacements have been made.
    
         ReplaceString = s ' return value
    End Function
    That's it. There are no empty strings passed to the function. The input string is complex simply because I build the SQL in the app being analyzed and documented within the VBA code for specific purposes. For example, the SQL given above asks for all records from the table tblImport, whose "ID" have a given value and whose Filename column ends with the extension ".enc". No big mystery, but it needed to be built within a routine that returned a group of files all having the same ImportID. But that app is another story. The question I have is about the app I am currently building that will analyze and document VBA code in any app I need to have it done.

    And, by "failed", it returns a result, just not the one I intended. I believe the issue is quotes within a string. VBA doesn't like unbalanced quotes and balances them if I remove one. As I stated earlier, I recall facing this problem years ago.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I use the Replace() function to escape apostrophe. Because quote marks gave me such headache, I FORBID use of quote mark by my users. Instead of quote mark for inch they have to use "in." or "inch". And I put this instruction in a label on forms. So far they obey me.

    However, I have since learned how to use the Replace function with quote mark. Say x = 22": Replace(x, """", """""") or Replace(x, """", "")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    TDinDC,
    Perhaps I'm missing something basic, or perhaps you haven't said it. You are taking vba code related to building valid (Access) SQL, parsing it and putting the result into a table in order to understand the purpose of that SQL string.

    What exactly makes this easier to understand? Could you provide an example along with some rationale?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You haven't reported any error messages, but I received one, so I need to ask if you have Option Explicit at the top of the module that contains your code. I think not?
    EDIT:
    OK, I believe I'm seeing the issue, which is, a double quote within a string (not the first one) is being treated as the end of a string, and the next double quote is being treated as the first double quote for a concatenation string that follows as a result. There is no concatenation string to the eye, but it is being formed as such. Thus
    Code:
    "SELECT * FROM tblImport WHERE (([ID] = " & ImportID & ") AND ([FileName] LIKE " & Chr$(34) & "*.enc" & Chr$(34) & "))"
    is being passed to the function as
    Code:
    "SELECT * FROM tblImport WHERE (([ID] = ) AND ([FileName] LIKE "*.enc"))"
    Note that the variables are not included, but only if I turn off Option Explicit will there be no error - otherwise they are treated as undeclared variables and an error is raised. The trick would seem to be how to get vba to treat the "internal" quotes as literals while accepting that the first and last are the string delimiters.

    I can see no way; not even the built in Replace function can handle this. It would be interesting to see what is returned to a string variable if the sql is treated as the sql property of a query def. Other than that, I have no other tangible ideas. There are other analysis and documenting tools in Access that can be used on queries, but I think this is at the point where we have to know why the original task is so necessary lest we spend time on trying to do what might be impossible when there's another solution. We can't begin to suggest alternatives because we don't know why the modified sql has to go into a table this way. If it's going to be 'analyzed' by eye, a trained eye probably doesn't need the quotes stripped out.
    Last edited by Micron; 07-18-2017 at 01:17 PM. Reason: added info

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I cannot get Access to accept the first sql statement posted in #5 (doesn't like the comma or double quotes around enc.) However, I can report some progress on the idea of using the sql property of a query def. If I start with
    Code:
    SELECT *
    FROM tblImport
    WHERE (([ID] = " & ImportID & ") AND ([FileName] LIKE " & Chr$(34) & '*enc' & Chr$(34) & "));
    I can replace double quotes (I chose singles):
    Code:
    SELECT *
    FROM tblImport
    WHERE (([ID] = ' & ImportID & ') AND ([FileName] LIKE ' & Chr$(34) & '*enc' & Chr$(34) & '));
    using
    Code:
    Sub TryQueryDef()
    Dim qdf As dao.QueryDef
    Dim strSql As String
    
    Set qdf = CurrentDb.QueryDefs("query20")
    Debug.Print qdf.sql
    strSql = Replace(qdf.sql, """", "'", 1)
    Debug.Print strSql
    
    End Sub
    So it appears it is possible. I guess where you'd go from here depends on whether or not the original replacement values can be used, or if you have to do it in stages and use a different replacement character that is easier to replace with the final desired replacement value. My guess is that it can be done in one step - and that a custom function is not required. You should be able to simply use the built in Replace function as I did.
    Last edited by Micron; 07-18-2017 at 03:22 PM. Reason: grammar
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Terminating a Process with VBA
    By kdbailey in forum Access
    Replies: 6
    Last Post: 02-23-2017, 08:08 AM
  2. Ask a process amounts
    By azhar2006 in forum Queries
    Replies: 4
    Last Post: 01-14-2014, 01:06 PM
  3. Need help to simplify this process
    By shanea.kr in forum Access
    Replies: 1
    Last Post: 07-10-2012, 01:40 PM
  4. Complex search through strings in records
    By pkstormy in forum Code Repository
    Replies: 2
    Last Post: 03-17-2012, 11:25 AM
  5. Efficient Process?
    By compooper in forum Database Design
    Replies: 1
    Last Post: 06-14-2011, 03:01 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