[WEB4LIB] MS Access and boolean search

[Chris Gray]

The following should work giving only records where the first search term
occurs in one of the three fields AND the second term appears in one of the
fields (not necessarily the same one).  The rearranged order and the
paranthesis are crucial.

SQL_query  = "SELECT * FROM Links
WHERE
(Description LIKE '%" & SearchA & "%'
OR
Title LIKE '%" & SearchA & "%'
OR
Keywords LIKE '%" & SearchA & "%')
AND
(Description LIKE '%" & SearchB & "%'
OR
Title LIKE '%" & SearchB & "%'
OR
Keywords LIKE '%" & SearchB & "%')"

Chris Gray
University of Waterloo
Library Systems


-----Original Message-----
From: web4lib at webjunction.org [mailto:web4lib at webjunction.org]On
Behalf Of Bryan H. Davidson
Sent: Friday, June 09, 2000 10:07 AM
To: Multiple recipients of list
Subject: [WEB4LIB] MS Access and boolean search

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