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.