String field contains "abc" using ASP to query Access database?

  • Thread starter Thread starter Noozer
  • Start date Start date
N

Noozer

I'm trying to create an SQL query to list all records where the field Name
contains the string "abc". For some reason I cannot figure out how to do
this.

SELECT * FROM MyTable WHERE CONTAINS(Name, "abc") > 0

....gives me an operator missing error.

Can someone show me the error of my ways?

: )
 
Noozer said:
I'm trying to create an SQL query to list all records where the field Name
contains the string "abc". For some reason I cannot figure out how to do
this.

SELECT * FROM MyTable WHERE CONTAINS(Name, "abc") > 0

...gives me an operator missing error.

Can someone show me the error of my ways?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access SQL doesn't support CONTAINS(). SQL Server can support CONTAINS,
but only if the db is set up for full-text indexing. Are you sure
you're using an Access/JET db?

If you want to try a standard SQL that Access/JET SQL supports, use
this:

SELECT * FROM MyTable WHERE Name Like "*abc*"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaNOIechKqOuFEgEQI6yACg+fWhHMZJtG1znxBzsyJs+7+flnQAoMXe
X/V9ceHhRDDwhXpQ4Wc1Xv7m
=59S2
-----END PGP SIGNATURE-----
 
MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access SQL doesn't support CONTAINS(). SQL Server can support CONTAINS,
but only if the db is set up for full-text indexing. Are you sure
you're using an Access/JET db?

If you want to try a standard SQL that Access/JET SQL supports, use
this:

SELECT * FROM MyTable WHERE Name Like "*abc*"
Click to expand...

Thanks... I wasn't having any luck with Google and CONTAINS was the closest
I could find. Definately using Access. This would explain the error. LIKE
will do just fine.

Thanks!
 
SELECT * FROM MyTable WHERE Name Like "*abc*"

Seems quite straightforward, but it doesn't seem to work...

This is the data in my sample table...
IDNum - Name - Account - Account 2
1234 - RName - 031 - 030
1234 - RName - 31 - 30
8200 - Steve - 00000000000 - 11111111111

And this query returns all the records...
SELECT * From MyTable WHERE true ORDER BY Added, RepNum ;

But this query returns no records...
SELECT * From MyTable WHERE true AND Name LIKE '*S*' ORDER BY Added, RepNum
;


I've also tried using "%" instead of "*" and double quotes instead of
singles around "*S*"
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what problem is. I'd get rid of the "WHERE true" and just use

WHERE Name LIKE '%S%'

I forgot you're using ASP & probably ADO. ADO uses the % in place of
the * wildcard.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmaYX4echKqOuFEgEQIhowCfYByHtq7G+k10KFzOgj3tB8WbUpkAmwa0
iRlJPuwhc5cjxCTu7caOSTqY
=I1oO
-----END PGP SIGNATURE-----
 
MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what problem is. I'd get rid of the "WHERE true" and just use

WHERE Name LIKE '%S%'
Click to expand...

Unfortunately the WHERE clause is generated dynamically. Starting with
"WHERE True" allows me to add more qualifiers to the query without a bunch
of testing whether or not I've already added something.
I forgot you're using ASP & probably ADO. ADO uses the % in place of
the * wildcard.
Click to expand...

Ya..but still didn't work. I'll have to dig a bit deeper.

Thanks!
 
Noozer said:
Unfortunately the WHERE clause is generated dynamically. Starting with
"WHERE True" allows me to add more qualifiers to the query without a bunch
of testing whether or not I've already added something.


Ya..but still didn't work. I'll have to dig a bit deeper.
Click to expand...

Not sure if this make a difference, but I change from

WHERE true AND Name LIKE '%S%'

....to...

WHERE (0=0) AND (Name LIKE '%S%')

Not sure what difference it makes but it works now.
 
Perhaps the SQL was using a LOGICAL AND vice the conjunction AND.

I suspect that if you had used

WHERE True = True AND [Name] Like ...

you would not have had the problem.
 
Noozer said:
Not sure if this make a difference, but I change from

WHERE true AND Name LIKE '%S%'

...to...

WHERE (0=0) AND (Name LIKE '%S%')

Not sure what difference it makes but it works now.
Click to expand...


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah ha... The word "true" was not evaluated as a Boolean True. The
expression 0=0 evaluates to a Boolean True. I'll bet the word "true"
evaluated to a negative or NULL value, which would be meaningless.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmcRh4echKqOuFEgEQKPjACfdiCP9uQLkeMzX5e2s2+ixFdwKTMAnikD
DJpwcjEnbxDnW3coT+AQcpPl
=mqHs
-----END PGP SIGNATURE-----
 
Back
Top