Complex Search Criteria

[dutrac6835]

Hi,

In Access 2010 I have two tables and I want to use the criteria from one table to search another table column. Let me give you an example...

Search Criteria
AR-15
AR-15A

Table to Search Within
Bla Bla Bla ar15
Bla Bla Bla ar 15
AR-15A Bla Bla Bla

Results need to look like this...
AR-15 Bla Bla Bla ar15
AR-15 Bla Bla Bla ar 15
AR-15A AR-15A Bla Bla Bla



Here is the existing search string as I have it. Like "*" & [Table to Search within]![Model] & "*"

The problem is that I cannot predict the string in "Table to Search Within." Search results do not return valid results because they are not always printed in the correct format. Only exactly "AR-15" will result in a hit. I want to break down the search so that it basically searches for AR-15 like this...

Select from "Table to Search Within" like "AR" and then "15"

In other words I want it to treat the "-" as a space and search the exact order. A record with data that says...
15AR would not be a hit
15-AR would not be a hit
15A-R would not be a hit
AR-215 would not be a hit

AR-15 would be
AR 15 would be

Any help would be appreciated.

[June7]

So possible combinations are AR-15, AR15, AR 15? (capital case doesn't matter)

Presuming Bla Bla Bla are fields, then AR-15 could be found in any field? Or is this one string

[dutrac6835]

Case doesn't matter.

Now that I re-read my post I understand what you are asking. The results would be in 2 columns. 1 would echo the search criteria before manipulation and the other would be the record where it found it.

[June7]

No, that's not what my question was going for. Your sample source data appears to shows the searched for string in two fields.

Are you confirming that the 3 forms of data possible? I should have been clearer with my question. I was make point that Access doesn't care about case, even though your sample shows both.

Unfortunately, I am stumped by your desired output. You show two search criteria (AR-15, AR-15A) and want to display these in field of the output. How is it to be determined which will display on each row?

This might require more than one query to get to the final output. I need a more complete sample of the tables. Does the field with the sample criteria data you present actually have other values in it? Does the search table have values other than AR-15?

[dutrac6835]

No, that's not what my question was going for. Your sample source data appears to shows the searched for string in two fields.

Are you confirming that the 3 forms of data possible? I should have been clearer with my question. I was make point that Access doesn't care about case, even though your sample shows both.

Unfortunately, I am stumped by your desired output. You show two search criteria (AR-15, AR-15A) and want to display these in field of the output. How is it to be determined which will display on each row?

This might require more than one query to get to the final output. I need a more complete sample of the tables. Does the field with the sample criteria data you present actually have other values in it? Does the search table have values other than AR-15?

Basically I'm searching all records of table 1 using multiple criteria (in a column) from table 2. I've created and attached two tables in Excel for demo purposes.

Results should be similar to what you see in Sheet 2 of "Table to Search Within.xls".

[June7]

Following is the best I can come up with in a query:
SELECT DISTINCT Search.Field1, Replace([Search]![Field1],"-","") AS NoHyphen, DLookUp("Field1","Criteria",Chr(34) & [NoHyphen] & Chr(34) & " Like " & Chr(34) & Chr(42) & Chr(34) & Chr(38) & " Replace([Field1],'-','')" & Chr(38) & Chr(34) & Chr(42) & Chr(34)) AS Criteria
FROM Search, Criteria
WHERE (((Replace([Search]![Field1],"-","")) Like "*" & Replace([Criteria]![Field1],"-","") & "*"));

One problem is with AR-15a. The string 'AR-15A Clean' is matched to AR-15 instead of AR-15A, which is not surprising. Same goes for AM-64a that matches to AM-64.

Also, can't deal with the space instead of hyphen in 'BR8 ES is good' so this record is not returned.

My assessment leads me to believe would need VBA to accomplish more. Even then, matching variable strings like this is never easy.

[dutrac6835]

Following is the best I can come up with in a query:
SELECT DISTINCT Search.Field1, Replace([Search]![Field1],"-","") AS NoHyphen, DLookUp("Field1","Criteria",Chr(34) & [NoHyphen] & Chr(34) & " Like " & Chr(34) & Chr(42) & Chr(34) & Chr(38) & " Replace([Field1],'-','')" & Chr(38) & Chr(34) & Chr(42) & Chr(34)) AS Criteria
FROM Search, Criteria
WHERE (((Replace([Search]![Field1],"-","")) Like "*" & Replace([Criteria]![Field1],"-","") & "*"));

One problem is with AR-15a. The string 'AR-15A Clean' is matched to AR-15 instead of AR-15A, which is not surprising. Same goes for AM-64a that matches to AM-64.

Also, can't deal with the space instead of hyphen in 'BR8 ES is good' so this record is not returned.

My assessment leads me to believe would need VBA to accomplish more. Even then, matching variable strings like this is never easy.

I agree with what you are saying, so I need to discover how to use VBA or better yet, VB to search the records. Do you or does anyone else know how to do that?

[June7]

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:

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.

[nbballard]

I hate to wake-up an old post- but I am trying to do something similar and encountered a problem.

When I attempt to use my 2 table structure to search for the values in my reference table, any value that causes a match is written to my result once for every value on the reference table.

Example:
I have 2 tables:
Table 1:
Value 1
Value 2
Value 3

Table 2:
Blah Blah Value1 Blah
Blah Blah
Blah Blah

What I want to receive back is:
Results:
Column 1: Value1
Column 2: Blah Blah Value1 Blah

But what I get is 3 rows, all containing "Blah Blah Value1 Blah" in column 2, but Column 1 contains all 3 values.

How do i need to format the request in Access so that the criteria is smart enough to pull the records based on a "Like *[Value]*", and have it also record what value the match was on in a seperate column?