Need Search Form Field to look at multiple tables

Hi everyone,

I need to figure out if there is a way to allow users to be able to type in "keywords" into one search field and that search be performed on 2 different fields within one table.

Here is the code I am currently using:

**************************
'-Build dynamic sql
sql = "SELECT ContractID, URL, Vendor, VendorWebsite, Description, Keywords FROM Contracts WHERE ((General)=(Yes))"

'--ContractID (partial search)
If Not IsEmpty(Request("ContractID")) Then
Dim strContractID
strContractID = Trim(Request("ContractID"))

If strContractID <> "" Then
sql = sql & "AND (ContractID LIKE '%" & Replace(strContractID, "'", "''") & "%') "
End If
End If

***********************

This repeats for several other fields, and based on the input into the search form, the user gets their results. I have the code working as long as I'm not trying to combine 2 fields.

I'm sure its possible. I just don't know how to do it. Any help is GREATLY appreciated.

Thanks!

Kellie

I think I figured it out. Here's what I changed:

'--Keyword (partial search)
If Not IsEmpty(Request("Keywords")) Then
Dim strKeywords
strKeywords = Trim(Request("Keywords"))

If strKeywords <> "" Then
sql = sql & "AND (Keywords LIKE '%" & Replace(strKeywords, "'", "''") & "%' OR Description LIKE '%" & Replace(strKeywords, "'", "''") & "%') "
End If
End If
********************************

Problem is, if the user inputs keywords from both Keywords and Description field, no results are displayed. If user puts in only one keyword, it produces results. If I change the OR to AND, I get no results at all.

What the heck am I doing wrong here

the problem is that when multiple keywords are inserted, the search is for an exact match of the phrase, which hardly ever exists.

You need to atomize the keywords (normally using either the space character, or one of the "boolean" characters) so that they are separated, and then create an even more "multiplied" SQL statement.

If the keywords field is filled in as "monkey elephant"
then you will need to generate the resulting SQL statement:
sql = sql & "AND (Keywords LIKE 'monkey' OR Keywords LIKE 'elephant' OR Description LIKE 'monkey'OR Description LIKE 'elephant') ", since you will probably never find "monkey elephant" in either the description or keywords fields together.