Re: The like clause of sql

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 myspammer1mail@xxxxxxxxxxx

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.


"Ken Snell [MVP]" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
news:O$ExmG5OFHA.3380@xxxxxxxxxxxxxxxxxxxxxxx
> 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?
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "sefs" <sefsinc@xxxxxxxxx> wrote in message
> news:%23wWgUy4OFHA.3380@xxxxxxxxxxxxxxxxxxxxxxx
> > 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.
> >
> >
> > "Ken Snell [MVP]" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
> > news:OVfpAn4OFHA.1564@xxxxxxxxxxxxxxxxxxxxxxx
> >> Just turn the expression around:
> >>
> >> SELECT Tablename.*
> >> FROM Tablename
> >> WHERE "lalafirstitemlala" Like "*" & Tablename.FieldName & "*";
> >>
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >> "sefs" <sefsinc@xxxxxxxxx> wrote in message
> >> news:%23%23HBHi4OFHA.3512@xxxxxxxxxxxxxxxxxxxxxxx
> >> > 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?
> >> >
> >> >
> >> > "Ken Snell [MVP]" <kthsneisllis9@xxxxxxxxxxxxxxxxxx> wrote in message
> >> > news:uvX$aa4OFHA.2384@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> SELECT Tablename.*
> >> >> FROM Tablename
> >> >> WHERE Tablename.FieldName Like "*" & "YourText" & "*";
> >> >>
> >> >> --
> >> >>
> >> >> Ken Snell
> >> >> <MS ACCESS MVP>
> >> >>
> >> >>
> >> >> "sefs" <sefsinc@xxxxxxxxx> wrote in message
> >> >> news:ujl0%23T4OFHA.1392@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >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.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>


.

Relevant Pages

  • Re: Field alias in sql query
    ... Instead of using SELECT fieldname AS myfield, ... That will look ugly in the query results, ... > I´m using a field alias in sql query. ...
    (microsoft.public.excel.programming)
  • Re: Parameters in Queries
    ... >name of the column in the 'Funds' table which the query is run against. ... be used to provide a *fieldname* to be searched. ... construct the SQL of the query, and then use that SQL as the ...
    (microsoft.public.access.queries)
  • Re: Upper Case
    ... You would create a new Query; open it in SQL view; and copy and paste this ... into that window. ... Change YourTable to the name of your table, and FieldName to ... create a new query based on your table. ...
    (microsoft.public.access.formscoding)
  • Re: How do I update specific records in a Access table with data f
    ... I pulled all the fields from the Master table and then changed ... When I ran the select query, I saw exactly what I wanted to ... >>I then added the Master table name in front of the fieldname in the update ... > Please open the Query in SQL view and post it here. ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)