"Query too Complex": MS Access Function to Replace text in Query (Nested Replace())

Question

Purpose: Use a SQL Query in MS Access to locate all records matching specific keywords in a long text field

I am attempting to query for all records in a MS Access DB that have a match on a list of specific keywords within a field. The keywords are as follows:

AIN, ATIN, CKD, AKI, ARF

Issue I'm running into is that the field is a free text entry field, so the formatting of the data is all over the place, and the keywords I'm searching on will often appear in the middle of other full length words (i.e. AIN matches on "pAIN","agAIN", etc), while I only want to include matches on words that are strictly the keywords (i.e. " AIN ", " AKI ").

The idea I'm working with is to simply include matches that will hit on the following format: field_name like '* AIN *'.So basically only include matches that have a space before and after the keyword to limit the number of false positives appearing in the result set.

I have tried writing a SQL query that will normalize the data so that all other characters that appear (".","!","?","#", etc...) will be replaced with a space character (i.e. " AIN!" would be replace(field_name,"!"," ") = " AIN ") with the idea that this should only include words containing only the keyword. In attempting to run my very long nested replace statement in the query, I am receiving the "Query Too Complex" message. Nested replace is as follows:

UCASE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.REF_CONTENT_NM,chr(13)," "),chr(10)," "),"`"," "),"~"," "),"!"," "),"@"," "),"#"," "),"$"," "),"%"," "),"^"," "),"&"," "),"*"," "),"("," "),")"," "),"-"," "),"_"," "),"="," "),"+"," "),"["," "),"{"," "),"]"," "),"}"," "),";"," "),":"," "),","," "),"<"," "),"."," "),">"," "),"/"," "),"?"," "),"\"," "),"|"," "),""""," ")) like "* AIN *"

I believe that a workaround would be to create a custom function that could be referenced in the SQL statement, but I am not entirely sure of how to accomplish this. So essentially, I am looking for guidance on how to evaluate a solution of how to normalize the text like the above nested replace statement in Access without running into the "Query Too Complex message". I feel like there is a simple solution that I am just not seeing here, so guidance would be tremendously appreciated!

I Think this Answer will solve your problem https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server — Ashraf Awad, Apr 20 '18 at 19:48
@AshrafAwad Eh no it won't. That's T-SQL, and not applicable to MS Access — Erik A, Apr 20 '18 at 19:49

Erik A · Answer 1 · 2018-04-20T20:17:31.590

The main trick to writing a custom function to do this is properly using the ParamArray

This is a small function that executes multiple replaces:

Public Function ReplaceMultiple(strInput As String, strReplace As String, ParamArray Find() As Variant) As String
    Dim i As Long
    ReplaceMultiple = strInput
    For i = LBound(Find) To UBound(Find)
        ReplaceMultiple = Replace(ReplaceMultiple, Find(i), strReplace)
    Next
End Function

Implement it:

ReplaceMultiple(a.REF_CONTENT_NM, " ", chr(13), chr(10), "`", "etc....")

You might need to think about altering the logic altogether, though, for example keeping a table of characters that should be replaced. I remember something about the max number of arguments being around 20-30, so you might need to use ReplaceMultiple twice.

If you just want to replace everything that isn't a string with a space, you can try the following small function:

Public Function ReplaceNonAlphanumeric(str As String) As String
    If str = "" Then Exit Function
    Dim i As Long
    For i = 1 To Len(str)
        If Mid(str, i, 1) Like "[0-9A-z]" Then
            ReplaceNonAlphanumeric = ReplaceNonAlphanumeric & Mid(str, i, 1)
        Else
            ReplaceNonAlphanumeric  = ReplaceNonAlphanumeric  & " "
        End If
    Next
End Function

Thank you so much for the help Erik! I believe that with your provided input I will be able to achieve the end result required. — Zac Broderick, Apr 23 '18 at 03:35

"Query too Complex": MS Access Function to Replace text in Query (Nested Replace())

1 Answers1