Custom Function Module - find records with specific Keywords (1 Viewer)

[adgjqetuo]

Hi - hoping someone can help me here.

I'm looking for some type of custom function that will search a specified column for any keywords listed inside another table.

I can run a query on each keyword individually, however there are 50 and it takes a long time each time I do it. I was hoping to write in a function for that column and it would just select all records that match.

These would all need to be a "like" with an " * " on each end of the word.

Tried searching but couldn't find much.



With SQL it would look something like:

select a.address1
from main_tbl as a
where a.address1 like '* north *'
or a.address1 like '* park *'
or a.address1 like '* south *';

I just want it to read each of the table values instead of hard coding them and the column name would be the function name so it can be used in any column I specify. I'm just not sure how to incorporate this into a custom function.

 

[Rx_]

Does this help?
Search1_AfterUpdate() ' After update event in text box Search 1 on the Home_2 form
example
' Searches multiple columns for a string fragment - highlights if found

30        Me.lst_id_wells = Null ' removed last filter
40     strSQL = "SELECT * from vWells_ListBox " & _
              "WHERE ((([well Name] & [state] & [status] & [API #] & [Permit_File_No] & [WLastKnown]) Like '*' & [Forms]![Home_2]![Search1] & '*')) ; " '
'     You must replace the list box and SQL Where clause to fit your needs
50        Me.lst_id_wells.RowSource = strSQL
60        Me.Refresh          
70       If Me.lst_id_wells.ListCount = 1 Then
80           Me.lst_id_wells.SetFocus
90           Me.lst_id_wells.Selected(0) = True
100       End If

 

[adgjqetuo]

Thanks for the help - after writing this, I just decided to write the thing in SQL and save it for future use.

SELECT f.*
FROM Keywords k, Main_Tbl f
where  (f.address1  like '*' & k.keyword & ' *')  OR  (f.address1  like '*' & k.keyword & ' *')
or  (f.address2  like '*' & k.keyword & ' *')  OR  (f.address2  like '*' & k.keyword & ' *')
or  (f.address3  like '*' & k.keyword & ' *')  OR  (f.address3  like '*' & k.keyword & ' *')
or  (f.company  like '*' & k.keyword & ' *')  OR  (f.company  like '*' & k.keyword & ' *')
or  (f.city  like '*' & k.keyword & ' *')  OR  (f.city  like '*' & k.keyword & ' *');