I have two tables: TBiology & tblTempSearchTextBiol
tblTempSearchTextBiol contains a single text field which stores user entered keywords, e.g.
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.
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
...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 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.
tblTempSearchTextBiol contains a single text field which stores user entered keywords, e.g.
Code:
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:
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
Code:
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"));
Anybody know how to do this using a query and a function?
Bob Skriver's code in 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.