like query

[a.mlw.walker]

can you run a like query that instead of picking two chosen words, i.s like "house" & "cottage"

instead it just checks to see if there are two words the same, in any order. and shows me duplicates, i.e if i had entries "radio (UK) 4" and "radio (wales) 4" as a duplicate query these arent the same, but can i run a like query just picks two words, so that they would come up.

Or something similar

thanks

Alex

 

[WayneRyan]

Alex,

You need the following syntax:

[Field] Like "*house*cottage*" Or [Field] Like "*cottage*house*"

But the problem with that is if you add a 3rd criteria, you're up to six
conditions surrounded by ORs. The 4th criteria gets you up to 16.

Alternatively, you can use criteria:

InStr([Field], "House") > 0 Or
InStr([Field], "Cottage") > 0 Or
InStr([Field], "Castle") > 0

That cuts down on the complexity.

Probably best in the long run would be to better define the process and
use a Public Function written in VBA.

Wayne

 

[a.mlw.walker]

No that was my example of the problem. I dont know the house/cottage bit, but i know there are entries with two words the same, so can i get it just to compare all combinations of two words, rather than an exact match, to find duplicates, rather than me entering which two words?

 

[Simon_MT]

If your entries were Radio 4 (UK) and Radio 4 (Wales) it would be simpler to deal with.

Simon

 

[neileg]

If I understand your question, the answers is no.

I presume what you want is to identify records that have any two words within a string of indeterimate length that match another record.

 

[WayneRyan]

Alex,

OK, now I understand.

You could traverse your table with a recordset:

Process all records.

For each record:
1) Split [YourField] Into "words" <-- delimited by a space
2) Search for any record where [YourField] has THAT WORD in it,
excluding the initial record itself.
3) Display in a message box any matching records.

Dim rst1 As DAO.RecordSet
Dim rst2 As DAO.RecordSet
Dim varArray As Variant
Dim i As Long

Set rst = CurrentDb.OpenRecordset("Select * From YourTable")

While Not rst1.EOF And Not rst1.BOF
   varArray = Split(rst1!YourField, " ")
   For i = 0 To UBound(varArray) - 1
      Set rst2 = CurrentDb.OpenRecordset("Select * " & _
                                         "From   YourTable " & _
                                         "Where [YourField] Like '*" & varArray(i) & "*' And " & _
                                         "      [YourPrimaryKey] <> " & rst1!YourPrimaryKey)
      While Not rst2.EOF And Not rst2.BOF
         MsgBox ("Similar Record For " & rst1!YourPrimaryKey & " - " & rst1!YourField & vbCrLf & _
                     "               " & rst2!YourPrimaryKey & " - " & rst2!YourField
         rst2.MoveNext
         Wend
      Next i
      rst1.MoveNext
   Wend

just a thought,
Wayne