The like clause of sql

  • Thread starter Thread starter Ken Snell [MVP]
  • Start date Start date
K

Ken Snell [MVP]

SELECT Tablename.*
FROM Tablename
WHERE Tablename.FieldName Like "*" & "YourText" & "*";
 
Just turn the expression around:

SELECT Tablename.*
FROM Tablename
WHERE "lalafirstitemlala" Like "*" & Tablename.FieldName & "*";


--

Ken Snell
<MS ACCESS MVP>
 
Errors? perhaps you should start at the beginning and describe exactly what
you're trying to do.

Are you using a form to enter a value and you want to build a query string
in code? Or are you trying to let a user enter a value when the query is
run? Or are you trying to write a query that will join two tables in order
to find matches between the two?

Are you trying to find tables with specific names for their fields? You're
not trying to find values in those fields?
 
OK -- ASP uses ADO, not DAO, for SQL syntax. % is the wildcard in ADO, while
* is the wildcard in DAO.

Dim strSQL
strSQL = "SELECT banemail .* FROM Tablename WHERE '" &
VariableWithSubmittedEmail & "' Like '%' & banemail.email & '%';"

--

Ken Snell
<MS ACCESS MVP>
 
I have a table that consist of rows of one field.

like this

1) firstitem
2) thisitem
3) thatitem

I want to compare the values in the table against another value, and if the
any value in the table is included as part of the other value of that other
value i am comparing it against its selected.

For example

If i compare firstitme against lalalalfirstitemlalala
then the first row is added to the selected list and so on.

What is the statemnt I should use with the LIKE clause

Best regards.
 
This dosnt work because....we would have

SELECT Tablename.*
FROM Tablename
WHERE Tablename.FieldName Like "*" & "lalafirstitemlala" & "*";

and if Tablename.FieldName = firstitem

then we would be saying select all records where firstitem LIKE
*lalafirstitemlala*

as you can see this is really backwards....

we want records to be selected if (a) the fieldname matches any part of the
value provided and not (b) the value matches any part of the fieldname.

Your example is (b) but we want to achieve (a)

Any more ideas?
 
Its not that simple because Tablename.FieldName is a fieldname
so you get errors

btw I am using this sql from ASP accessing MS Access.

I have tried
"SELECT Count(Email) FROM banemail WHERE (%" & email & "% LIKE '" & txtEmail
& "')"
but this generates an error to the point that email is undefined which it is
because email will now be translated as a variable and not a field name,
which makes sense.

So then I have tried
"SELECT Count(Email) FROM banemail WHERE (%" & "email" & "% LIKE
'testvaluegoeshere')"

but then i get Syntax error in query expression '(%email% LIKE
'testvaluegoeshere')'

What am i doing wrong there.
 
I am using Active Server Pages to access Microsoft Access

I have a table called banemail with two fields called id, and email

into the email field i have inputed some words for instance for one of the
recoreds I have spammer

so the table looks like this....

ID EMAIL
1 Spammer1
2 Spammer2


Now I have a form on a webpage that accepts emails that are submitted to the
website.

so say i submit an email (e-mail address removed)

What I want the sql to do is to compare that submitted email against values
in the banemail table so that if any value in banemail table forms any part
of an email submitted that record is selected.

So I though I could do this with the LIKE clause of sql in a ASP sql
statement.
 
Perfect!!! Thank you very much.

Best Regards.

Ken Snell said:
OK -- ASP uses ADO, not DAO, for SQL syntax. % is the wildcard in ADO, while
* is the wildcard in DAO.

Dim strSQL
strSQL = "SELECT banemail .* FROM Tablename WHERE '" &
VariableWithSubmittedEmail & "' Like '%' & banemail.email & '%';"
Click to expand...
 
Back
Top