Keyword Searches

[snaily]

Hi,
I am trying to perform some keyword searches of a text field. My preferred option would be to hold all the keywords in a separate table and then use something like
SELECT * FROM Text Table, Keywords Table WHERE (((InStr[Text Table].[Text],[Keywords Table].[keywords])) > 0 ));
This works and returns anything which has the string from the keyword table, however I wanted to look for whole words only. I can do this in SQL using a regular expression along the lines of [!a-z] keyword [!a-z] which only finds the string where it has no letters directly either side of it.
What I would like to do is combine the two methods so I hold my keywords in a table and then use them with the reg ex to find whole words only. Thanks in advance for any advice.
Brian

 

[Galaxiom]

Use a Like condition in the join to narrow the search.

This can only be done in SQL View as it is not supported in the Design View.

SELECT whatever
FROM DataTable
INNER JOIN KeywordTable
ON DataTable.SearchedField Like "*" & KeywordTable.KeyWordField & "*"

Then use your Regular Expression in the Where clause.

 

[snaily]

Use a Like condition in the join to narrow the search.

This can only be done in SQL View as it is not supported in the Design View.

SELECT whatever
FROM DataTable
INNER JOIN KeywordTable
ON DataTable.SearchedField Like "*" & KeywordTable.KeyWordField & "*"

Then use your Regular Expression in the Where clause.

Thanks Galaxiom,
Please bear with me as I am completely new to Access as of two days ago! I have created your query above and it is bringing back what I would expect without the regular expressions e.g I have ole in my keyword search and I only want ole as a whole word not pole for instance. How do I include the where clause to use the regular expressions I need?
Thanks again in advance.

 

[Galaxiom]

Sorry I thought you had a working expression.

Try this in the Join:

ON DataTable.SearchedField Like "* " & KeywordTable.KeyWordField & " *"
OR DataTable.SearchedField Like KeywordTable.KeyWordField & " *"
OR DataTable.SearchedField Like "* " & KeywordTable.KeyWordField
OR DataTable.SearchedField = KeywordTable.KeyWordField

Note the spaces which mean it must be a separate word. The extra lines cover the possibility of the keyword being at the beginning or end or is exactly equal to the keyword.

BTW Full Regular Expression syntax is available by using a custom function. There is an example function here.

However this is slow because it must be processed by Access rather than the database engine. Could be useful if the search is narrowed with the join first.

 

[snaily]

Perfect Galaxiom. Have it working just how I want with a minor tweak to pick up words that have a full stop or other non-alpha character after them. Thanks for your help would have taken me ages to figure it out otherwise!

 

[Galaxiom]

Try this untested suggestion:

ON DataTable.SearchedField Like "* " & KeywordTable.KeyWordField & "[. ]*"
OR DataTable.SearchedField Like KeywordTable.KeyWordField & "[. ]*"
OR DataTable.SearchedField Like "* " & KeywordTable.KeyWordField
OR DataTable.SearchedField = KeywordTable.KeyWordField

 

[snaily]

Try this untested suggestion:

ON DataTable.SearchedField Like "* " & KeywordTable.KeyWordField & "[. ]*"
OR DataTable.SearchedField Like KeywordTable.KeyWordField & "[. ]*"
OR DataTable.SearchedField Like "* " & KeywordTable.KeyWordField
OR DataTable.SearchedField = KeywordTable.KeyWordField

Thanks.
This works perfectly for one of my fields. Unfortunately the other field I would like to search in is a memo field and it appears you can't join on a memo. I realise this is probably for performance issues but the data I am searching each day is only around 500 records so wondered if there wa any way around this?