CONTAINS statement in SQL

[chris1977uk]

i am using ASP and an access database, and i'm trying to do a search facility using the CONTAINS statement, but i keep getting this error.-

Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'CONTAINS(cusemail, 'chris', 1) > 0'.


the whole statement in sql is.-

set objRSManu = server.CreateObject("ADODB.Recordset")
sql = "SELECT score(1), orderdate, cusemail from Orders WHERE CONTAINS(cusemail, 'chris', 1) > 0"
objRSManu.open sql , Conn

this search should return all the records that have "chris" in the email address, what am i doing wrong!!

[chris1977uk]

been told this should work too.-

SELECT id FROM orders WHERE CONTAINS (cusemail, 'chris') > 0

but it doesn't

please help

[doctorgonzo]

CONTAINS can't be used in Access databases. It can only be used with SQL Server. The version of SQL that Access uses does not have that functionality; at least my version of Access 2000 doesn't.

[chris1977uk]

cheers doctor, i think you are right!

i have however tried it with SQL Server as well, though i didnt get an error message after making the table full-text indexed, it doesnt return any records either..

any ideas anyone?

[doctorgonzo]

What is the ">0" doing there? Have you tried using it without the >0?

[chris1977uk]

yeah i did try it without it

its something to do with scores, and how the contains operator grades its matches, if theres a match it gives it an index greater than 0, the example i "borrowed" the statement from had that clause in.

[doctorgonzo]

Hmm, the T-SQL reference I have for SQL Server doesn't say much about weighting.

Excactly what records are you trying to return? Have you tried using a wildcard? For example, if you want to return all records where cusemail starts with "chris", use "SELECT id FROM orders WHERE CONTAINS (cusemail, 'chris*')

If all else fails, could LIKE work? For example, "SELECT id FROM orders WHERE cusemail LIKE 'chris*'" I usually use LIKE in queries.

[chris1977uk]

not tried it with * yet, but i will do

got some like statements that work, and they work in Access too !!

[doctorgonzo]

Yes, LIKE works with Acess SQL, which is why I tend to use them more (I started with Access and then moved to SQL Server).