search criteria filter

[anna_717717]

Hi all,

Hope somebody can help

I've got a table which includes a field called 'keywords'. In a form I have
a control into which I enter data and use a like comparison to filter the
continous form,based on the table, by the keywords field.
I would like to be able to filter by more than 1 keyword. Rather than having
multiple controls to search for multiple keywords is it possible to enter all
the search criteria into 1 control and filter the keywords from this?


Thanks

 

[Keith Wilby]

Hi all,

Hope somebody can help

I've got a table which includes a field called 'keywords'. In a form I
have
a control into which I enter data and use a like comparison to filter the
continous form,based on the table, by the keywords field.
I would like to be able to filter by more than 1 keyword. Rather than
having
multiple controls to search for multiple keywords is it possible to enter
all
the search criteria into 1 control and filter the keywords from this?


Thanks

How about using the built-in "filter by form"?

Keith.
www.keithwilby.co.uk

 

[Dale_Fye via AccessMonster.com]

Possible, yes. More complicated, yes.

I generally create a function when I want to do this. Something like:

Public Function fnContainsKeyWords(TextToSearch as Variant,
KeyWords as string,
Optional SearchHow as
String = "Any") as boolean

Dim aKeyWords() as String
Dim intLoop as integer
Dim intCharPos as integer
Dim bFound as boolean

fnContainsKeyWords = False
If ISNULL(TextToSearch) then
Exit Function
ElseIF Len(KeyWords & "") = 0 then
Exit Function
End IF

bFound = true
aKeyWords = Split(KeyWords, " ")
For intLoop = lbound(aKeyWords) to ubound(aKeyWords)
intCharPos = instr(TextToSearch, alltrim(aKeyWords(intLoop)))
if intCharPos = 0 and SearchHow = "All" Then
fnContainsKeyWords = False
Exit Function
elseif intCharPos > 0 and SearchHow = "Any" Then
fnContainsKeyWords = True
Exit Function
else
bFound = bFound AND ((intCharPos) > 0)
End IF
Next
fnContainsKeyWords = bFound

End Function

Then, you just call this function in your query:

SELECT * FROM YourTable
WHERE fnContainsKeyWords([SomeField], "text1 text2 text3", "Any") = true

or you could pass the function a reference to the control on your form where
you have entered the key words.

HTH
Dale