Hi,
I am trying to create a search page which will allow a quick search using 
one text box, to take a line such as " Database Design " and then have the 
database column called "keywords" searched for any records with these two 
words in.

If I try and search the database does not return anything unless there is 
one word in the record and the word is entered exactly into the field.
For example, record 1 has keywords of "design" only. If I enter "design" 
it returns the results showing record 1 as having the keyword.
Record 2 has two keywords, "database design". I cannot get the results to 
come back if i enter anything, such as "design", "database design", 
or "database".

I have used the LIKE %, but cannot get anywhere with more than two words, 
so looked around on forums and found the replace function.

The code I have tried is below:
strSQL = "select * from resources" & _ 
         " where keywords LIKE '" & Replace(Request.Form
("textfield")," ", " *") & "'"

Not really sure if I am tackling this the correct way, so any thoughts 
would be very much appreciated.

Thanks in anticpation,
Jo

If someone enters "Database Design" do you want to return records:
a) that have the keywords "Database" or "Design"
b) that have the keywords "Database" and "Design"
c) that have the keywords "Database Design" only

Please explain your requirements *exactly*, as it is quite difficult to
suggest a solution otherwise.

In answer to your existing question though, where the records contain the
keywords "Database Design", and you type in the criteris "Database", your
SQL needs to look like this:

strCriteria = "Database"
strSQL = _
    "SELECT field1, field2, field3 " & _
    "FROM myTable " & _
    "WHERE keywords LIKE '%" & strCriteria & "%'"

% is a wildcard for 1 or more characters

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <jodytregenna@y...>
Subject: [access_asp] search database


: I am trying to create a search page which will allow a quick search using
: one text box, to take a line such as " Database Design " and then have the
: database column called "keywords" searched for any records with these two
: words in.
:
: If I try and search the database does not return anything unless there is
: one word in the record and the word is entered exactly into the field.
: For example, record 1 has keywords of "design" only. If I enter "design"
: it returns the results showing record 1 as having the keyword.
: Record 2 has two keywords, "database design". I cannot get the results to
: come back if i enter anything, such as "design", "database design",
: or "database".
:
: I have used the LIKE %, but cannot get anywhere with more than two words,
: so looked around on forums and found the replace function.
:
: The code I have tried is below:
: strSQL = "select * from resources" & _
:          " where keywords LIKE '" & Replace(Request.Form
: ("textfield")," ", " *") & "'"
:
: Not really sure if I am tackling this the correct way, so any thoughts
: would be very much appreciated.

Hi Ken (or anyone else)

Exact requirements:
If someone enters "Database Design" or "Design Database" for example I 
want the search to find all records which have the following possibilities 
as their keywords:

1) that have the keywords "Database" or "Design"
2) that have the keywords "Database" and "Design"
3) that have the keywords "Database Design"

(These keywords could be in any order though, not necessarily as the 
database holds them)

As an example of the contents of a record, the keywords column 
has, "Oracle Design Database Databases Code".

I have tried the code with %% as you wrote and as before if I just 
type "oracle" as the keyword to find and it returns the record.
If I enter "database oracle" or "databases oracle" the search returns 
nothing. Is this because the words are not in the same sequence as in the 
record?

regards
Jo

Try this.
>strSQL = "select * from resources" & _
>          " where keywords LIKE '%" & Request.Form
>("textfield")&"%'"
>





>From: jodytregenna@y...
>Reply-To: "Access ASP" <access_asp@p...>
>To: "Access ASP" <access_asp@p...>
>Subject: [access_asp] search database
>Date: Mon, 3 Feb 2003 23:27:44
>
>Hi,
>I am trying to create a search page which will allow a quick search using
>one text box, to take a line such as " Database Design " and then have the
>database column called "keywords" searched for any records with these two
>words in.
>
>If I try and search the database does not return anything unless there is
>one word in the record and the word is entered exactly into the field.
>For example, record 1 has keywords of "design" only. If I enter "design"
>it returns the results showing record 1 as having the keyword.
>Record 2 has two keywords, "database design". I cannot get the results to
>come back if i enter anything, such as "design", "database design",
>or "database".
>
>I have used the LIKE %, but cannot get anywhere with more than two words,
>so looked around on forums and found the replace function.
>
>The code I have tried is below:
>strSQL = "select * from resources" & _
>          " where keywords LIKE '" & Replace(Request.Form
>("textfield")," ", " *") & "'"
>
>Not really sure if I am tackling this the correct way, so any thoughts
>would be very much appreciated.
>
>Thanks in anticpation,
>Jo


_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail

Q) Is this because the words are not in the same sequence as in the record?
A) Yes.

You are doing a search like this:

SELECT Field1, Field2 FROM myTable WHERE KeyWords LIKE '%database oracle%'

which will match any record that has:

<any text here>database oracle<any text here>

but no other records. What you will need to do is *split* the search
criteria so that your SQL statement looks like:

SELECT
    Field1, Field2
FROM
    myTable
WHERE
    KeyWord LIKE '%Oracle%'
OR
    KeyWord LIKE '%Database%'

If the user enters three words, like "Oracle Database Design", then your SQL
statement will need to look like:

SELECT
    Field1, Field2
FROM
    myTable
WHERE
    KeyWord LIKE '%Oracle%'
OR
    KeyWord LIKE '%Database%'
OR
    KeyWord LIKE '%Design%'

So, you need to use Split() in your ASP code to split the search criteria.
Then, depending on the UBound() of the resulting array, you need to append a
WHERE clause, and any optional OR clauses.

Cheers
Ken




~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: <jodytregenna@y...>
To: "Access ASP" <access_asp@p...>
Sent: Tuesday, February 04, 2003 9:48 AM
Subject: [access_asp] Re: search database


: Hi Ken (or anyone else)
:
: Exact requirements:
: If someone enters "Database Design" or "Design Database" for example I
: want the search to find all records which have the following possibilities
: as their keywords:
:
: 1) that have the keywords "Database" or "Design"
: 2) that have the keywords "Database" and "Design"
: 3) that have the keywords "Database Design"
:
: (These keywords could be in any order though, not necessarily as the
: database holds them)
:
: As an example of the contents of a record, the keywords column
: has, "Oracle Design Database Databases Code".
:
: I have tried the code with %% as you wrote and as before if I just
: type "oracle" as the keyword to find and it returns the record.
: If I enter "database oracle" or "databases oracle" the search returns
: nothing. Is this because the words are not in the same sequence as in the
: record?
:
: regards
: Jo