SQL Help please

[Paul M]

Hi
I need help to refine my search please
For instance I can search for "sea" and it return "seaside" which is good,
but I can search for "man" and it will return "woman" which is not good. I
only wish it to wildcard the end of the word not the beginning.
Thankyou
Paul M

Here is my sql

strSQL = "SELECT image_number, image_name, image_desc " _
& "FROM tblimages " _
& "WHERE image_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR image_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR image_number LIKE '" & Replace(strSearch, "'", "''") & "' " _
& "ORDER BY image_number DESC;"

 

[Ronx]

Remove the wildcard from the beginning of each term:

strSQL = "SELECT image_number, image_name, image_desc " _
& "FROM tblimages " _
& "WHERE image_desc LIKE '" & Replace(strSearch, "'", "''") & "%' " _
& "OR image_name LIKE '" & Replace(strSearch, "'", "''") & "%' " _
& "OR image_number LIKE '" & Replace(strSearch, "'", "''") & "' " _
& "ORDER BY image_number DESC;"

Your image number term will require the exact image number (assuming
digits are used).

 

[Paul M]

Thanks Ron
But I have problem I can now put in man and man is returned not woman but if
I put in seaside which there are lots of it returns nothing and if I put in
sea it should it should return all the seaside images but it is returning
only images with sea in the title and not in the image description field
which is basically just key words.
In the access database I have seperated key words like this

sea, seaside, woman

with a comma and a space,is this the correct way to do it?

I have set up a test page below

http://www.paulcmilner.co.uk/art/defaultsearch2.asp?search=seaside

and this is the normal untouched page
http://www.paulcmilner.co.uk/art/defaultsearch.asp?search=seaside

Thanks
Paul M

 

[Thomas A. Rowe]

Instead of using Woman, Women, use ladies, lady.

Remove the spaces from your keyword field

--
==============================================
Thomas A. Rowe
Microsoft MVP - FrontPage

http://www.Ecom-Data.com
==============================================

 

[Rob Giordano \(Crash\)]

ot;
Nice content!



--
~~~~~~~~~~~~~~~~~~
Rob Giordano
Microsoft MVP Expression





| Thanks Ron
| But I have problem I can now put in man and man is returned not woman but
if
| I put in seaside which there are lots of it returns nothing and if I put
in
| sea it should it should return all the seaside images but it is returning
| only images with sea in the title and not in the image description field
| which is basically just key words.
| In the access database I have seperated key words like this
|
| sea, seaside, woman
|
| with a comma and a space,is this the correct way to do it?
|
| I have set up a test page below
|
| http://www.paulcmilner.co.uk/art/defaultsearch2.asp?search=seaside
|
| and this is the normal untouched page
| http://www.paulcmilner.co.uk/art/defaultsearch.asp?search=seaside
|
| Thanks
| Paul M
|
|
|
| | > Remove the wildcard from the beginning of each term:
| >
| > strSQL = "SELECT image_number, image_name, image_desc " _
| > & "FROM tblimages " _
| > & "WHERE image_desc LIKE '" & Replace(strSearch, "'", "''") & "%' " _
| > & "OR image_name LIKE '" & Replace(strSearch, "'", "''") & "%' " _
| > & "OR image_number LIKE '" & Replace(strSearch, "'", "''") & "' " _
| > & "ORDER BY image_number DESC;"
| >
| > Your image number term will require the exact image number (assuming
| > digits are used).
| > --
| > Ron Symonds - Microsoft MVP (FrontPage)
| > Reply only to group - emails will be deleted unread.
| >
| > http://www.rxs-enterprises.org/fp
| >
| >
| >
| >
| > | >
| >> Hi
| >> I need help to refine my search please
| >> For instance I can search for "sea" and it return "seaside" which is
| >> good,
| >> but I can search for "man" and it will return "woman" which is not
good.
| >> I
| >> only wish it to wildcard the end of the word not the beginning.
| >> Thankyou
| >> Paul M
| >>
| >> Here is my sql
| >>
| >> strSQL = "SELECT image_number, image_name, image_desc " _
| >> & "FROM tblimages " _
| >> & "WHERE image_desc LIKE '%" & Replace(strSearch, "'", "''") & "%' "
_
| >> & "OR image_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
| >> & "OR image_number LIKE '" & Replace(strSearch, "'", "''") & "' " _
| >> & "ORDER BY image_number DESC;"
| >
|
|

 

[Ronx]

Try this:
strSQL = "SELECT image_number, image_name, image_desc " _
& "FROM tblimages " _
& "WHERE image_desc LIKE '% " & Replace(strSearch, "'", "''") & "%' "
_
& "OR image_name LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "OR image_number LIKE '% " & Replace(strSearch, "'", "''") & "' " _
& "ORDER BY image_number DESC;"

This will search for words beginning with whatever is in the search box.
Note that the first (and every) keyword must have a space in front of
it. The image name does not need the space, but the search will pick up
Woman if man is used. Or add the space (image_name LIKE '% " ) and all
the image names in the database will require a space in front.

If "man" is searched for, "woman" will not be found. The comma between
keywords is irrelevant, but spaces must be immediately before the
keyword:

Sea, woman, something - woman can be found.
Sea ,woman, something - woman will not be found since there is no space
before the w. Note the space in front of "Sea".

You can probably add the spaces before the first words with an update
query in Access - I have not tried this.
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

 

[Paul M]

Thanks Rob Much appreciated
Paul M

 

[Paul M]

"Ron Symonds to the rescue"!!!
Thanks Ron
Paul M