Yes, I know how. A custom function could be called from query. Input argument for the function would be the string to search in and the return would be the matched criteria, if found. The function would match criteria like AR-15A before AR-15. Probably make use of VBA coded recordset in the function. Open recordset of criteria table, sorted descending order. Compare the input with each value of recordset (without hyphens or spaces) until match or end of file (then no match found). Something like:
Code:
Public Function MatchCriteria(strSearch As String) As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Criteria ORDER BY Field1 DESC;", cn, adOpenStatic, adLockReadOnly
strSearch = Replace(Replace(strSearch, "-", ""), " ", "")
Do While Not rs.EOF
If InStr(strSearch, Replace(Replace(rs!Field1, "-", ""), " ", "")) > 0 Then MatchCriteria = rs!Field1
If MatchCriteria <> "" Then
Exit Do
Else
rs.MoveNext
End If
Loop
rs.Close
End Function
Be sure to select the VBA reference: Microsoft ActiveX Data Objects 2.x Library
The function is placed in a general code module. Call the function in expression of query that has the Search table as source.
Criteria: MatchCriteria([fieldname])
This worked with the sample data you provided. Be aware that searching on variable strings is tricky and variance from the pattern indicatd by your sample will probably produce erroneous results.
Be very careful. Can get caught in endless loop and have to kill the app with Task Manager (I did several times). This will happen if the necessary reference library is not selected as well as if code is not right.