CODEWITHSUNDEEP
sqlvbams-access
A R
A R

Reputation: 13

How can I make a search query disregard punctuation?

I'm making a database to track periodicals in an archive. All I have is Microsoft Access 2007-2016.

The goal of this query is a search function that searches multiple fields, that doesn't require the exact punctuation used (in this case, it will work if the punctuation in the search term is exact, OR if there is no punctuation in the search term).

I get an error message

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.

By copying 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

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

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

Upvotes: 1

Views: 53

Answers (1)

FunThomas
FunThomas

Reputation: 29586

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 ...

Upvotes: 2

Related Questions