MS Access and boolean search

Bryan H. Davidson bdavidso at comp.uark.edu
Fri Jun 9 10:07:52 EDT 2000


All -

Maybe someone out there has done this already and might be able to give me
some advice.

I'm trying to create a search box that allows users to do a single Boolean
AND search on an Access database of web links. The three fields searched are
the "Description" "Title" and "Keyword" field.
(See it in action @ http://dante.uark.edu/eresources/webresources.asp)

Currently I'm cheating:

if the user types in an AND, the script pulls out the AND and does an OR
search on the two terms (which I figured would be better than returning all
the results with the word AND in them!)

The code I have written so far does the following:
1) Looks for the word AND
2) If there is an AND, it pull out the words before and after the AND
3) The variable SearchA = the first word |||| the variable SearchB = the
second word.
4) The following query is then performed on the three fields (a big OR
search).

___________________________
SQL_query  = "SELECT * FROM Links WHERE Description LIKE '%" & SearchA & "%'
OR Description LIKE '%" & SearchB & "%'

OR Title LIKE '%" & SearchA & "%' OR Title LIKE '%" & SearchB & "%'

OR Keywords LIKE '%" & SearchA & "%' OR Keywords LIKE '%" & SearchB & "%'"
____________________________

If I start throwing AND's around in the above statement, the search will be
too restrictive; nor will it be a true Boolean AND search on all three
fields. Correct?

I'm afraid that to do a true Boolean AND search on all three fields would
require quite a long and complex statement. Any thoughts here?

If you are interested, the entire piece of code is below (don't laugh).
I know it is probably a bit screwy, but I'm new at this.
____________________________
'''' IF TWO TERMS WERE ENTERED, SPLIT INTO TWO VARIABLES
Else If Instr(Searchstring," ") > "0" Then
SearchA = Left(Searchstring, Instr(Searchstring, " "))
SearchB = Mid(Searchstring, Instr(Searchstring, " "))

'''' LOOK FOR THE WORD -AND-

SearchC = Mid(Searchstring, Instr(Searchstring, " "),4)
 If SearchC = " and" then

'''' IF THERE IS AN -AND- CHOP IT OFF

 SearchB = Mid(Searchstring, (Instr(Searchstring, " ")+5))

 End if


'''' QUERY FOR MULTIPLE TERMS
SQL_query  = "SELECT * FROM Links WHERE Description LIKE '%" & SearchA & "%'
OR Description LIKE '%" & SearchB & "%' OR Title LIKE '%" & SearchA & "%' OR
Title LIKE '%" & SearchB & "%' OR Keywords LIKE '%" & SearchA & "%' OR
Keywords LIKE '%" & SearchB & "%'"

Else

'''' QUERY FOR A SINGLE TERM
SQL_query  = "SELECT * FROM Links WHERE Description LIKE '%" & Searchstring
& "%' OR Title LIKE '%" & Searchstring & "%' OR Keywords LIKE '%" &
Searchstring & "%'"
____________________________

Thanks a million.
~~~~~~~~~~~~~~~~~~~~~
Bryan H. Davidson
Electronic Products Librarian / Webmaster
University of Arkansas Libraries, Fayetteville
Ph. 501-575-4665



More information about the Web4lib mailing list