If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

sql syntax problem

I have run out of ways to write this sql statement and I still can't get it
to work. I am entering text in a text box and trying to find records that
match the search text. My sql statement looks like this:
"select * from streets where streetname like '" & sText & "*" & " '"
If "M" is the search text, I want to see all records that start w/ "M". However,
it is showing a recordcount of 0. I assume this is just a syntax issue, can
anyone please help?

Re: sql syntax problem

"tedk" <tkirkendal@aol.com> wrote:
>
>I have run out of ways to write this sql statement and I still can't get
it
>to work. I am entering text in a text box and trying to find records that
>match the search text. My sql statement looks like this:
>"select * from streets where streetname like '" & sText & "*" & " '"
>If "M" is the search text, I want to see all records that start w/ "M".
However,
>it is showing a recordcount of 0. I assume this is just a syntax issue,
can
>anyone please help?
>
>thanks, Ted

Re: sql syntax problem

The problem here is the space in the last set of quotation marks. You are
actually searching for all strings such as "Mark " and "Mary " but not "Mark"
and "Mary" because the later two don't contain that stray space.

"tedk" <tkirkendal@aol.com> wrote:
>
>I have run out of ways to write this sql statement and I still can't get
it
>to work. I am entering text in a text box and trying to find records that
>match the search text. My sql statement looks like this:
>"select * from streets where streetname like '" & sText & "*" & " '"
>If "M" is the search text, I want to see all records that start w/ "M".
However,
>it is showing a recordcount of 0. I assume this is just a syntax issue,
can
>anyone please help?
>
>thanks, Ted

Re: sql syntax problem

TedK,

In case it still doesn't work... perhaps you can try this:

"SELECT * FROM streets WHERE streetname LIKE '" & sText & "%'"

Samuel Jackson

"G. Allen" <gallenspam@worldnet.att.net> wrote:
>
>The problem here is the space in the last set of quotation marks. You are
>actually searching for all strings such as "Mark " and "Mary " but not "Mark"
>and "Mary" because the later two don't contain that stray space.
>
>You need to use:
>"select * from streets where streetname like '" & sText & "*" & "'"
>
>"tedk" <tkirkendal@aol.com> wrote:
>>
>>I have run out of ways to write this sql statement and I still can't get
>it
>>to work. I am entering text in a text box and trying to find records that
>>match the search text. My sql statement looks like this:
>>"select * from streets where streetname like '" & sText & "*" & " '"
>>If "M" is the search text, I want to see all records that start w/ "M".
>However,
>>it is showing a recordcount of 0. I assume this is just a syntax issue,
>can
>>anyone please help?
>>
>>thanks, Ted
>

Re: sql syntax problem

"tedk" <tkirkendal@aol.com> wrote:
>
>I have run out of ways to write this sql statement and I still can't get
it
>to work. I am entering text in a text box and trying to find records that
>match the search text. My sql statement looks like this:
>"select * from streets where streetname like '" & sText & "*" & " '"
>If "M" is the search text, I want to see all records that start w/ "M".
However,
>it is showing a recordcount of 0. I assume this is just a syntax issue,
can
>anyone please help?
>
>thanks, Ted

Re: sql syntax problem

"tedk" <tkirkendal@aol.com> wrote in message
news:3b09544f$1@news.devx.com...
>
> I have run out of ways to write this sql statement and I still can't get
it
> to work. I am entering text in a text box and trying to find records that
> match the search text. My sql statement looks like this:
> "select * from streets where streetname like '" & sText & "*" & " '"
> If "M" is the search text, I want to see all records that start w/ "M".
However,
> it is showing a recordcount of 0. I assume this is just a syntax issue,
can
> anyone please help?

Are you using Access? If so, then your SQL looks fine.
If you are using another database, then you may need a different wildcard.
Eg. SQL Server uses %

Re: sql syntax problem

"Adelle Hartley" <adelle@sql-guru.com> wrote:
>"tedk" <tkirkendal@aol.com> wrote in message
>news:3b09544f$1@news.devx.com...
>>
>> I have run out of ways to write this sql statement and I still can't get
>it
>> to work. I am entering text in a text box and trying to find records that
>> match the search text. My sql statement looks like this:
>> "select * from streets where streetname like '" & sText & "*" & " '"
>> If "M" is the search text, I want to see all records that start w/ "M".
>However,
>> it is showing a recordcount of 0. I assume this is just a syntax issue,
>can
>> anyone please help?
>
>Are you using Access? If so, then your SQL looks fine.
>If you are using another database, then you may need a different wildcard.
>Eg. SQL Server uses %
>
>Adelle.
>_____________________________________________________________
>Impress your friends. Become an SQL guru.
>http://www.sql-guru.com
>
>
The SQL statement generated will be:
select * from streets where streetname like 'M* '
The space in the literal could be your problem.

Re: sql syntax problem

"Richard" <richard.Turnbull@au.unisys.com> wrote:
>
>"Adelle Hartley" <adelle@sql-guru.com> wrote:
>>"tedk" <tkirkendal@aol.com> wrote in message
>>news:3b09544f$1@news.devx.com...
>>>
>>> I have run out of ways to write this sql statement and I still can't
get
>>it
>>> to work. I am entering text in a text box and trying to find records
that
>>> match the search text. My sql statement looks like this:
>>> "select * from streets where streetname like '" & sText & "*" & " '"
>>> If "M" is the search text, I want to see all records that start w/ "M".
>>However,
>>> it is showing a recordcount of 0. I assume this is just a syntax issue,
>>can
>>> anyone please help?
>>
>>Are you using Access? If so, then your SQL looks fine.
>>If you are using another database, then you may need a different wildcard.
>>Eg. SQL Server uses %
>>
>>Adelle.
>>_____________________________________________________________
>>Impress your friends. Become an SQL guru.
>>http://www.sql-guru.com
>>
>>
>The SQL statement generated will be:
>select * from streets where streetname like 'M* '
>The space in the literal could be your problem.
>

Like this you are looking for records containing EXACTLY three characters:
M, * and a space.

The correct syntax is 'M%'. This will give you all strings starting with
an M.

Re: sql syntax problem

This is just an ideia but try replacing the "*" by a "%".

You will end up with a string sort of like this
"SELECT * FROM streets WHERE streetname LIKE '" & sText & "%'"

"Richard" <richard.Turnbull@au.unisys.com> wrote:
>
>"Adelle Hartley" <adelle@sql-guru.com> wrote:
>>"tedk" <tkirkendal@aol.com> wrote in message
>>news:3b09544f$1@news.devx.com...
>>>
>>> I have run out of ways to write this sql statement and I still can't
get
>>it
>>> to work. I am entering text in a text box and trying to find records
that
>>> match the search text. My sql statement looks like this:
>>> "select * from streets where streetname like '" & sText & "*" & " '"
>>> If "M" is the search text, I want to see all records that start w/ "M".
>>However,
>>> it is showing a recordcount of 0. I assume this is just a syntax issue,
>>can
>>> anyone please help?
>>
>>Are you using Access? If so, then your SQL looks fine.
>>If you are using another database, then you may need a different wildcard.
>>Eg. SQL Server uses %
>>
>>Adelle.
>>_____________________________________________________________
>>Impress your friends. Become an SQL guru.
>>http://www.sql-guru.com
>>
>>
>The SQL statement generated will be:
>select * from streets where streetname like 'M* '
>The space in the literal could be your problem.
>