Query - trying to find 'like' data

[CherylH]

Hi!
What kind of expression can I write to pick this up? I used the 'like'
operator but that won't pick this up in my query. Essentially, I am trying
to pick up anything that even closely matches (or may contain one word) that
matches within both tables.

Table A Table B
Late Charges Late Charge Amount

Thanks for any help!

 

[Jeff Boyce]

Cheryl

We aren't there. We can't see your data. "How" depends on "what".

Consider posting a description of your underlying table structure (i.e.,
fields and contents - examples help), and post the SQL statement of your
query.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer (MVP)]

You will need to use VBA to build the where clause / the entire query.
Or you could use a custom function - it would be slow.

UNTESTED AIRCODE FUNCTION to be pasted into a module

Public Function fAnyMatch(strA, strB) As Integer
Dim s As Variant
Dim i As Long
Dim iMatch as Long
fAnyMatch = False


If Len(strA & "") = 0 Or Len(strB & "") = 0 Then
iMatch = 0
Else
While InStr(1, strB, " ", vbTextCompare) > 0
strB = Replace(strB, " ", " ")
Wend

s = Split(strB, " ")
For i = LBound(s) To UBound(s)
If strA & "" Like "*" & s(i) & "*" Then
iMatch = imatch +1
End If
Next i
End If
fAnyMatch = iMatch
End Function

In the query you would use something like this in query design view to get the
matching records.

Field: fAnyMatch([TableA].[SomeField],[TableB].[AnotherField])
Criteria: > 0

For your specific example the function would return 2 - Late like Late and
Charges like Charge


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County