test memo field for multiple keywords from table 1

[MePenguin]

I have two tables: TBiology & tblTempSearchTextBiol

tblTempSearchTextBiol contains a single text field which stores user entered keywords, e.g.

CriteriaItem
water
wood

TBiology contains about 250K records of extracted text data in a memo field (field:data), with a CODE identifier field (not unique - as every code can have several extracted texts) and Reference field (the latter irrelevant here).
e.g.

CODE,	Data,	Ref
01.00100200,	on sandy Calluna heath,Koch 1989

I'm trying to create a query(s) that will allow me to select all the CODE's from TBiology where any of the records in tblTempSearchTextBiol are found within the TBiology data.

So far I can use VBA to construct & save the query on the fly into as something like

SELECT DISTINCT TBiology.CODE
FROM TBiology
WHERE (((TBiology.Data) Like "*water*")) OR (((TBiology.Data) Like "*wood*"));

or even

SELECT DISTINCT TBiology.CODE
FROM TBiology
WHERE (((InStr(1,[TBiology].[data],"water"))>"0")) OR (((InStr(1,[TBiology].[data],"wood"))>"0"));

...adding extra 'OR's as necessary. But i don't like this method, it seems too risky in terms of aukward characters and max length of SQL.

Anybody know how to do this using a query and a function?
Bob Skriver's code in

http://www.tek-tips.com/viewthread.cfm?qid=473531

gets me close, but not quite... my problem is the reverse...

Thanks,

Phil

PS. Data has to be in a memo field - it's text extracted from publications, so no normalisation possible.

Phil

---------------
Pass me the ether.

 

[Golom]

I would probably go with a UDF

Public Function WordFound (MemoField As String, ParamArray Words() As Variant) As Boolean
WordFound = False
Dim X As Variant
For Each X in Words
   If Instr(1, MemoField, X) > 0 Then
      WordFound = True
      Exit For
   End If
Next
Exit Function

Then

Select DISTINCT TBiology.Code
From TBiology
Where WordFound (TBiology.[Data], "water", "wood") = TRUE

You can of course construct the SQL in code and append as many arguments as you like to the test.

 

[PHV]

What have I misunderstood ?
SELECT DISTINCT TBiology.CODE
FROM TBiology, tblTempSearchTextBiol
WHERE TBiology.Data Like '*' & tblTempSearchTextBiol.CriteriaItem & '*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MePenguin]

You have missed absolutely nothing, PHV, as always.

I, on the other hand, obviously missed that I should have taken a break and come back to the problem later. A clear case of a subconscious desire to make things more complicated than they really are?..

Have star for having your head in the right place! (And for solving the problem, of course)


Golom - your solution looks like it would work, thank's for the input - could be useful.

Regards,


Phil

---------------
Pass me the ether.