How can I simplify this expression to make a search query disregard punctuation? Microsoft Access 2007-2016

I'm making a database to keep track of periodicals in an archive, and all I have to work with is Microsoft Access 2007-2016. I've pretty much had to teach myself how to use Access for the most part, so I apologize in advance if I'm missing something obvious.

The goal of this query is to have a search function that searches multiple fields, but doesn't require the person searching to know the exact punctuation used (in this case, it will work if the punctuation in the search term is exactly right, OR if there is no punctuation at all in the search term).

Unfortunately, when I try to run the code I have, I get an error message that says "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

The problem is, I'm not sure how to do that... Any specific advice or even links to tutorials would be appreciated!

By copying some functions suggested on other forum posts, this is what I have so far:

The Query:

SELECT 

tblSerials.Title, 
tblSerials.[Shelf Location], 
tblSerials.City, 
tblSerials.[Publisher/Associated Organization], 
tblSerials.[Audience/Genre], 
tblSerials.Microfilm, 
tblSerials.Notes, 
tblSerials.Language, 
tblSerials.[bib ctrl number], 
tblSerials.[Year Range], 
tblSerials.[Storage Notes], 
tblSerials.Barcode

FROM tblSerials

WHERE 
(((tblSerials.Title) Like "*" & [Enter Search Term] & "*")) 
Or ((((MultiReplace(AllowOnly(tblSerials.title,""),"-"," ","/"," "))) Like "*" & [Enter Search Term] & "*")) 

Or (((tblSerials.[Publisher/Associated Organization]) Like "*" & [Enter Search Term] & "*")) 
Or ((((MultiReplace(AllowOnly(tblSerials.[Publisher/Associated Organization],""),"-"," ","/"," "))) Like "*" & [Enter Search Term] & "*")) 

Or ((((tblSerials.notes)) Like "*" & [Enter Search Term] & "*")) 
Or ((((MultiReplace(AllowOnly(tblSerials.notes,""),"-"," ","/"," "))) Like "*" & [Enter Search Term] & "*"))

ORDER BY Replace([tblSerials].Title,' ','');

The VBA Functions:

Option Compare Database


Public Function AllowOnly(yourstring As String, Allowed As String)

If Allowed = "" Then Allowed = " -/1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim intLoop As Integer
For intLoop = 1 To Len(yourstring)
    If InStr(Allowed, Mid(yourstring, intLoop, 1)) > 0 Then
        AllowOnly = AllowOnly & Mid(yourstring, intLoop, 1)
    End If
Next intLoop

End Function

Public Function MultiReplace(varInput, ParamArray varReplacements())
    ' call like this:
    ' MultiReplace("abcdefghijk","c","V","e","X","j","Y","m","Z")
    ' to return this:
    ' abVdXfghiYk

    Const MESSAGETEXT = "Uneven number of replacements parameters."
    Dim n As Integer
    Dim varOutput As Variant
    Dim intParamsCount As Integer
    
    If Not IsNull(varInput) Then
        intParamsCount = UBound(varReplacements) + 1
        If intParamsCount Mod 2 = 0 Then
            varOutput = varInput
            For n = 0 To UBound(varReplacements) Step 2
                varOutput = Replace(varOutput, varReplacements(n), varReplacements(n + 1))
            Next n
        Else
            MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
        End If
    End If
            
    MultiReplace = varOutput
End Function

The reason I couldn't use JUST the AllowOnly function is because I needed certain punctuation marks to be replaced with spaces instead of just being removed entirely.

I can get the ((((MultiReplace(AllowOnly(tblSerials.title,""),"-"," ","/"," "))) bit to work by itself, but when I combine it with all the rest, I get the error message.

Solution

First step: Optimize (a little bit) your VBA code. It is good that you have 2 separate functions, keep it like it is. A small improvement to AllowOnly: declare the 2nd parameter as Optional so that you don't have to pass an empty string as argument when you want to access the default list of allowed characters:

Public Function AllowOnly(yourstring As String, Optional Allowed As String = "")

Now create a short function that combines the call to the 2 functions, using the default replacement characters that you use in your query.

Public Function HarmonizeString(yourstring) As String
    If IsNull(yourstring) Then Exit Function
    HarmonizeString = MultiReplace(Allowonly(CStr(yourstring)), "-", " ", "/", " ")
End Function

With that, you can simplify the terms of your query to

HarmonizeString(tblSerials.[Publisher/Associated Organization])

(of course you can choose any other name)

Second step: Create a simple Query in Access that selects all Data from your Input table (tblSerials) and adds the harmonized search fields. Query definition (SQL view) could look like this

SELECT 
      tblSerials.title, 
      HarmonizeString([title]) AS HarmonizedTitle,
      tblSerials.[Publisher/Associated Organization],
      HarmonizeString(tblSerials.[Publisher/Associated Organization]) As HarmonizedPublisher
      (...)
FROM tblSerials;

I named it querySerials, but again choose your own name.

Now the Query you want to issue can be reduced to

Select (...)
    From querySerials
Where  
    ((querySerials.Title Like "*" & [Enter Search Term] & "*")
  Or (querySerials.HarmonizedTitle Like "*" & [Enter Search Term] & "*"))
  Or ...