Query

I have used the query designed for the Select All values by using the
wildcard "*" and using the word "contain". The problem that I am having now
is that if I select Virginia, records with West Virginia are showing up. If I
use the criteria using the word "equal" then the select all query results are
not being displayed. Does anyone have any clues?

Kevin
Wed Mar 09 08:48:26 CST 2005

Let's have a look at the actual query.

The behavior you're describing is correct for a wildcard search. I don't
know anything about your requirements, however. If you want to search for a
state, how about using a drop-down list box? One of the purposes of a
drop-down list box is to prevent user error in typing in search values.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
What You Seek Is What You Get.

"L" <L@discussions.microsoft.com> wrote in message
news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
>I have used the query designed for the Select All values by using the
> wildcard "*" and using the word "contain". The problem that I am having
> now
> is that if I select Virginia, records with West Virginia are showing up.
> If I
> use the criteria using the word "equal" then the select all query results
> are
> not being displayed. Does anyone have any clues?

L
Wed Mar 09 08:59:07 CST 2005

I do have a drop down list with all the states including the option of
"Select All". But when I use the DRW and use contain, if I select Virginia, I
get Virginia and West Virgina.

Here's the code from DRW:
fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
AND ContactState LIKE '%::ContactState::%')"
fp_sDefault="ContactState=&ContactState="


"Kevin Spencer" wrote:

> Let's have a look at the actual query.
>
> The behavior you're describing is correct for a wildcard search. I don't
> know anything about your requirements, however. If you want to search for a
> state, how about using a drop-down list box? One of the purposes of a
> drop-down list box is to prevent user error in typing in search values.
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> ..Net Developer
> What You Seek Is What You Get.
>
> "L" <L@discussions.microsoft.com> wrote in message
> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> >I have used the query designed for the Select All values by using the
> > wildcard "*" and using the word "contain". The problem that I am having
> > now
> > is that if I select Virginia, records with West Virginia are showing up.
> > If I
> > use the criteria using the word "equal" then the select all query results
> > are
> > not being displayed. Does anyone have any clues?
>
>
>

Stefan
Wed Mar 09 10:04:02 CST 2005

That is the correct result for contain (like) Virginia
- since Virginia is in Virginia and West Virginia

To limit to Only Virginia you need to delete the AND
fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::')"

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp
_____________________________________________


"L" <L@discussions.microsoft.com> wrote in message news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
|I do have a drop down list with all the states including the option of
| "Select All". But when I use the DRW and use contain, if I select Virginia, I
| get Virginia and West Virgina.
|
| Here's the code from DRW:
| fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
| AND ContactState LIKE '%::ContactState::%')"
| fp_sDefault="ContactState=&ContactState="
|
|
| "Kevin Spencer" wrote:
|
| > Let's have a look at the actual query.
| >
| > The behavior you're describing is correct for a wildcard search. I don't
| > know anything about your requirements, however. If you want to search for a
| > state, how about using a drop-down list box? One of the purposes of a
| > drop-down list box is to prevent user error in typing in search values.
| >
| > --
| > HTH,
| >
| > Kevin Spencer
| > Microsoft MVP
| > ..Net Developer
| > What You Seek Is What You Get.
| >
| > "L" <L@discussions.microsoft.com> wrote in message
| > news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
| > >I have used the query designed for the Select All values by using the
| > > wildcard "*" and using the word "contain". The problem that I am having
| > > now
| > > is that if I select Virginia, records with West Virginia are showing up.
| > > If I
| > > use the criteria using the word "equal" then the select all query results
| > > are
| > > not being displayed. Does anyone have any clues?
| >
| >
| >

L
Wed Mar 09 10:11:07 CST 2005

I understand but when I do that the Select All returns no records.

"Stefan B Rusynko" wrote:

> That is the correct result for contain (like) Virginia
> - since Virginia is in Virginia and West Virginia
>
> To limit to Only Virginia you need to delete the AND
> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::')"
>
> _____________________________________________
> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> "Warning - Using the F1 Key will not break anything!" (-;
> To find the best Newsgroup for FrontPage support see:
> http://www.net-sites.com/sitebuilder/newsgroups.asp
> _____________________________________________
>
>
> "L" <L@discussions.microsoft.com> wrote in message news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> |I do have a drop down list with all the states including the option of
> | "Select All". But when I use the DRW and use contain, if I select Virginia, I
> | get Virginia and West Virgina.
> |
> | Here's the code from DRW:
> | fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
> | AND ContactState LIKE '%::ContactState::%')"
> | fp_sDefault="ContactState=&ContactState="
> |
> |
> | "Kevin Spencer" wrote:
> |
> | > Let's have a look at the actual query.
> | >
> | > The behavior you're describing is correct for a wildcard search. I don't
> | > know anything about your requirements, however. If you want to search for a
> | > state, how about using a drop-down list box? One of the purposes of a
> | > drop-down list box is to prevent user error in typing in search values.
> | >
> | > --
> | > HTH,
> | >
> | > Kevin Spencer
> | > Microsoft MVP
> | > ..Net Developer
> | > What You Seek Is What You Get.
> | >
> | > "L" <L@discussions.microsoft.com> wrote in message
> | > news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> | > >I have used the query designed for the Select All values by using the
> | > > wildcard "*" and using the word "contain". The problem that I am having
> | > > now
> | > > is that if I select Virginia, records with West Virginia are showing up.
> | > > If I
> | > > use the criteria using the word "equal" then the select all query results
> | > > are
> | > > not being displayed. Does anyone have any clues?
> | >
> | >
> | >
>
>
>

L
Wed Mar 09 10:13:09 CST 2005

With that statement "SELECT * FROM tblCompany WHERE (ContactState =
'::ContactState::')"
the Select All value returns no records. And I have the wildcard % as the
value in the dropdown.

"Stefan B Rusynko" wrote:

> That is the correct result for contain (like) Virginia
> - since Virginia is in Virginia and West Virginia
>
> To limit to Only Virginia you need to delete the AND
> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::')"
>
> _____________________________________________
> SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
> "Warning - Using the F1 Key will not break anything!" (-;
> To find the best Newsgroup for FrontPage support see:
> http://www.net-sites.com/sitebuilder/newsgroups.asp
> _____________________________________________
>
>
> "L" <L@discussions.microsoft.com> wrote in message news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> |I do have a drop down list with all the states including the option of
> | "Select All". But when I use the DRW and use contain, if I select Virginia, I
> | get Virginia and West Virgina.
> |
> | Here's the code from DRW:
> | fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
> | AND ContactState LIKE '%::ContactState::%')"
> | fp_sDefault="ContactState=&ContactState="
> |
> |
> | "Kevin Spencer" wrote:
> |
> | > Let's have a look at the actual query.
> | >
> | > The behavior you're describing is correct for a wildcard search. I don't
> | > know anything about your requirements, however. If you want to search for a
> | > state, how about using a drop-down list box? One of the purposes of a
> | > drop-down list box is to prevent user error in typing in search values.
> | >
> | > --
> | > HTH,
> | >
> | > Kevin Spencer
> | > Microsoft MVP
> | > ..Net Developer
> | > What You Seek Is What You Get.
> | >
> | > "L" <L@discussions.microsoft.com> wrote in message
> | > news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> | > >I have used the query designed for the Select All values by using the
> | > > wildcard "*" and using the word "contain". The problem that I am having
> | > > now
> | > > is that if I select Virginia, records with West Virginia are showing up.
> | > > If I
> | > > use the criteria using the word "equal" then the select all query results
> | > > are
> | > > not being displayed. Does anyone have any clues?
> | >
> | >
> | >
>
>
>

Kevin
Wed Mar 09 10:36:09 CST 2005

Okay, again, the purpose of a drop-down list is to prevent errors from the
user. This means that any value in the drop-down list should be the exact
state name in the database. If so, what is the need for a wildcard search?


Also, let me analyze your query:

fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
AND ContactState LIKE '%::ContactState::%')"

Translated into English, this means that the ContactState field must have
the exact state name in it, AND it should contain the name. Of course, if it
has the exact state name, it DOES contain the name, so this is redundant. It
should read:

fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
'::ContactState::' "

If you want to search ALL states, you add an option whose value is "*"

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
What You Seek Is What You Get.

"L" <L@discussions.microsoft.com> wrote in message
news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
>I do have a drop down list with all the states including the option of
> "Select All". But when I use the DRW and use contain, if I select
> Virginia, I
> get Virginia and West Virgina.
>
> Here's the code from DRW:
> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> '::ContactState::'
> AND ContactState LIKE '%::ContactState::%')"
> fp_sDefault="ContactState=&ContactState="
>
>
> "Kevin Spencer" wrote:
>
>> Let's have a look at the actual query.
>>
>> The behavior you're describing is correct for a wildcard search. I don't
>> know anything about your requirements, however. If you want to search for
>> a
>> state, how about using a drop-down list box? One of the purposes of a
>> drop-down list box is to prevent user error in typing in search values.
>>
>> --
>> HTH,
>>
>> Kevin Spencer
>> Microsoft MVP
>> ..Net Developer
>> What You Seek Is What You Get.
>>
>> "L" <L@discussions.microsoft.com> wrote in message
>> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
>> >I have used the query designed for the Select All values by using the
>> > wildcard "*" and using the word "contain". The problem that I am having
>> > now
>> > is that if I select Virginia, records with West Virginia are showing
>> > up.
>> > If I
>> > use the criteria using the word "equal" then the select all query
>> > results
>> > are
>> > not being displayed. Does anyone have any clues?
>>
>>
>>

L
Wed Mar 09 10:53:04 CST 2005

I have that option in my drop down list. My drop down list has all the states
and an option for Select All which has a value of "*". We want our users to
either view data for a specific state or to view information for all the
states.

"Kevin Spencer" wrote:

> Okay, again, the purpose of a drop-down list is to prevent errors from the
> user. This means that any value in the drop-down list should be the exact
> state name in the database. If so, what is the need for a wildcard search?
>
>
> Also, let me analyze your query:
>
> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
> AND ContactState LIKE '%::ContactState::%')"
>
> Translated into English, this means that the ContactState field must have
> the exact state name in it, AND it should contain the name. Of course, if it
> has the exact state name, it DOES contain the name, so this is redundant. It
> should read:
>
> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
> '::ContactState::' "
>
> If you want to search ALL states, you add an option whose value is "*"
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> ..Net Developer
> What You Seek Is What You Get.
>
> "L" <L@discussions.microsoft.com> wrote in message
> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> >I do have a drop down list with all the states including the option of
> > "Select All". But when I use the DRW and use contain, if I select
> > Virginia, I
> > get Virginia and West Virgina.
> >
> > Here's the code from DRW:
> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> > '::ContactState::'
> > AND ContactState LIKE '%::ContactState::%')"
> > fp_sDefault="ContactState=&ContactState="
> >
> >
> > "Kevin Spencer" wrote:
> >
> >> Let's have a look at the actual query.
> >>
> >> The behavior you're describing is correct for a wildcard search. I don't
> >> know anything about your requirements, however. If you want to search for
> >> a
> >> state, how about using a drop-down list box? One of the purposes of a
> >> drop-down list box is to prevent user error in typing in search values.
> >>
> >> --
> >> HTH,
> >>
> >> Kevin Spencer
> >> Microsoft MVP
> >> ..Net Developer
> >> What You Seek Is What You Get.
> >>
> >> "L" <L@discussions.microsoft.com> wrote in message
> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> >> >I have used the query designed for the Select All values by using the
> >> > wildcard "*" and using the word "contain". The problem that I am having
> >> > now
> >> > is that if I select Virginia, records with West Virginia are showing
> >> > up.
> >> > If I
> >> > use the criteria using the word "equal" then the select all query
> >> > results
> >> > are
> >> > not being displayed. Does anyone have any clues?
> >>
> >>
> >>
>
>
>

L
Wed Mar 09 10:57:18 CST 2005

Also, if I use Like then if a user selects Virginia then records associated
West Virgina are displayed as well and that makes sense b/c I'm using like
and not equal. I need a user to select Virgina and only Virgina shows and for
a user to have an option to Select all and view records for all states.

"Kevin Spencer" wrote:

> Okay, again, the purpose of a drop-down list is to prevent errors from the
> user. This means that any value in the drop-down list should be the exact
> state name in the database. If so, what is the need for a wildcard search?
>
>
> Also, let me analyze your query:
>
> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState = '::ContactState::'
> AND ContactState LIKE '%::ContactState::%')"
>
> Translated into English, this means that the ContactState field must have
> the exact state name in it, AND it should contain the name. Of course, if it
> has the exact state name, it DOES contain the name, so this is redundant. It
> should read:
>
> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
> '::ContactState::' "
>
> If you want to search ALL states, you add an option whose value is "*"
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> ..Net Developer
> What You Seek Is What You Get.
>
> "L" <L@discussions.microsoft.com> wrote in message
> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> >I do have a drop down list with all the states including the option of
> > "Select All". But when I use the DRW and use contain, if I select
> > Virginia, I
> > get Virginia and West Virgina.
> >
> > Here's the code from DRW:
> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> > '::ContactState::'
> > AND ContactState LIKE '%::ContactState::%')"
> > fp_sDefault="ContactState=&ContactState="
> >
> >
> > "Kevin Spencer" wrote:
> >
> >> Let's have a look at the actual query.
> >>
> >> The behavior you're describing is correct for a wildcard search. I don't
> >> know anything about your requirements, however. If you want to search for
> >> a
> >> state, how about using a drop-down list box? One of the purposes of a
> >> drop-down list box is to prevent user error in typing in search values.
> >>
> >> --
> >> HTH,
> >>
> >> Kevin Spencer
> >> Microsoft MVP
> >> ..Net Developer
> >> What You Seek Is What You Get.
> >>
> >> "L" <L@discussions.microsoft.com> wrote in message
> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> >> >I have used the query designed for the Select All values by using the
> >> > wildcard "*" and using the word "contain". The problem that I am having
> >> > now
> >> > is that if I select Virginia, records with West Virginia are showing
> >> > up.
> >> > If I
> >> > use the criteria using the word "equal" then the select all query
> >> > results
> >> > are
> >> > not being displayed. Does anyone have any clues?
> >>
> >>
> >>
>
>
>

Kevin
Wed Mar 09 12:37:13 CST 2005

> Also, if I use Like then if a user selects Virginia then records
> associated
> West Virgina are displayed as well and that makes sense b/c I'm using like
> and not equal.

I co-authored a book on SQL a couple of years ago. Did you try my
recommendation?

The LIKE operator is case-insensitive, and allows for the use of wildcards.
If you don't use any wildcards, it works just like a case-insensitive =
operator.

Also, read my nessage carefully. I also told you how to handle the SELECT
ALL case. In case you have difficulty finding it:

>> If you want to search ALL states, you add an option whose value is "*"

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
What You Seek Is What You Get.

"L" <L@discussions.microsoft.com> wrote in message
news:F49132CC-57D4-4B9B-8C2E-4EA8CA2093D2@microsoft.com...
> Also, if I use Like then if a user selects Virginia then records
> associated
> West Virgina are displayed as well and that makes sense b/c I'm using like
> and not equal. I need a user to select Virgina and only Virgina shows and
> for
> a user to have an option to Select all and view records for all states.
>
> "Kevin Spencer" wrote:
>
>> Okay, again, the purpose of a drop-down list is to prevent errors from
>> the
>> user. This means that any value in the drop-down list should be the exact
>> state name in the database. If so, what is the need for a wildcard
>> search?
>>
>>
>> Also, let me analyze your query:
>>
>> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
>> '::ContactState::'
>> AND ContactState LIKE '%::ContactState::%')"
>>
>> Translated into English, this means that the ContactState field must have
>> the exact state name in it, AND it should contain the name. Of course, if
>> it
>> has the exact state name, it DOES contain the name, so this is redundant.
>> It
>> should read:
>>
>> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
>> '::ContactState::' "
>>
>> If you want to search ALL states, you add an option whose value is "*"
>>
>> --
>> HTH,
>>
>> Kevin Spencer
>> Microsoft MVP
>> ..Net Developer
>> What You Seek Is What You Get.
>>
>> "L" <L@discussions.microsoft.com> wrote in message
>> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
>> >I do have a drop down list with all the states including the option of
>> > "Select All". But when I use the DRW and use contain, if I select
>> > Virginia, I
>> > get Virginia and West Virgina.
>> >
>> > Here's the code from DRW:
>> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
>> > '::ContactState::'
>> > AND ContactState LIKE '%::ContactState::%')"
>> > fp_sDefault="ContactState=&ContactState="
>> >
>> >
>> > "Kevin Spencer" wrote:
>> >
>> >> Let's have a look at the actual query.
>> >>
>> >> The behavior you're describing is correct for a wildcard search. I
>> >> don't
>> >> know anything about your requirements, however. If you want to search
>> >> for
>> >> a
>> >> state, how about using a drop-down list box? One of the purposes of a
>> >> drop-down list box is to prevent user error in typing in search
>> >> values.
>> >>
>> >> --
>> >> HTH,
>> >>
>> >> Kevin Spencer
>> >> Microsoft MVP
>> >> ..Net Developer
>> >> What You Seek Is What You Get.
>> >>
>> >> "L" <L@discussions.microsoft.com> wrote in message
>> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
>> >> >I have used the query designed for the Select All values by using the
>> >> > wildcard "*" and using the word "contain". The problem that I am
>> >> > having
>> >> > now
>> >> > is that if I select Virginia, records with West Virginia are showing
>> >> > up.
>> >> > If I
>> >> > use the criteria using the word "equal" then the select all query
>> >> > results
>> >> > are
>> >> > not being displayed. Does anyone have any clues?
>> >>
>> >>
>> >>
>>
>>
>>

L
Wed Mar 09 12:57:15 CST 2005

Kevin,

I will try your suggestion and let you know what happens.


"Kevin Spencer" wrote:

> > Also, if I use Like then if a user selects Virginia then records
> > associated
> > West Virgina are displayed as well and that makes sense b/c I'm using like
> > and not equal.
>
> I co-authored a book on SQL a couple of years ago. Did you try my
> recommendation?
>
> The LIKE operator is case-insensitive, and allows for the use of wildcards.
> If you don't use any wildcards, it works just like a case-insensitive =
> operator.
>
> Also, read my nessage carefully. I also told you how to handle the SELECT
> ALL case. In case you have difficulty finding it:
>
> >> If you want to search ALL states, you add an option whose value is "*"
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> ..Net Developer
> What You Seek Is What You Get.
>
> "L" <L@discussions.microsoft.com> wrote in message
> news:F49132CC-57D4-4B9B-8C2E-4EA8CA2093D2@microsoft.com...
> > Also, if I use Like then if a user selects Virginia then records
> > associated
> > West Virgina are displayed as well and that makes sense b/c I'm using like
> > and not equal. I need a user to select Virgina and only Virgina shows and
> > for
> > a user to have an option to Select all and view records for all states.
> >
> > "Kevin Spencer" wrote:
> >
> >> Okay, again, the purpose of a drop-down list is to prevent errors from
> >> the
> >> user. This means that any value in the drop-down list should be the exact
> >> state name in the database. If so, what is the need for a wildcard
> >> search?
> >>
> >>
> >> Also, let me analyze your query:
> >>
> >> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> >> '::ContactState::'
> >> AND ContactState LIKE '%::ContactState::%')"
> >>
> >> Translated into English, this means that the ContactState field must have
> >> the exact state name in it, AND it should contain the name. Of course, if
> >> it
> >> has the exact state name, it DOES contain the name, so this is redundant.
> >> It
> >> should read:
> >>
> >> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
> >> '::ContactState::' "
> >>
> >> If you want to search ALL states, you add an option whose value is "*"
> >>
> >> --
> >> HTH,
> >>
> >> Kevin Spencer
> >> Microsoft MVP
> >> ..Net Developer
> >> What You Seek Is What You Get.
> >>
> >> "L" <L@discussions.microsoft.com> wrote in message
> >> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> >> >I do have a drop down list with all the states including the option of
> >> > "Select All". But when I use the DRW and use contain, if I select
> >> > Virginia, I
> >> > get Virginia and West Virgina.
> >> >
> >> > Here's the code from DRW:
> >> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> >> > '::ContactState::'
> >> > AND ContactState LIKE '%::ContactState::%')"
> >> > fp_sDefault="ContactState=&ContactState="
> >> >
> >> >
> >> > "Kevin Spencer" wrote:
> >> >
> >> >> Let's have a look at the actual query.
> >> >>
> >> >> The behavior you're describing is correct for a wildcard search. I
> >> >> don't
> >> >> know anything about your requirements, however. If you want to search
> >> >> for
> >> >> a
> >> >> state, how about using a drop-down list box? One of the purposes of a
> >> >> drop-down list box is to prevent user error in typing in search
> >> >> values.
> >> >>
> >> >> --
> >> >> HTH,
> >> >>
> >> >> Kevin Spencer
> >> >> Microsoft MVP
> >> >> ..Net Developer
> >> >> What You Seek Is What You Get.
> >> >>
> >> >> "L" <L@discussions.microsoft.com> wrote in message
> >> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> >> >> >I have used the query designed for the Select All values by using the
> >> >> > wildcard "*" and using the word "contain". The problem that I am
> >> >> > having
> >> >> > now
> >> >> > is that if I select Virginia, records with West Virginia are showing
> >> >> > up.
> >> >> > If I
> >> >> > use the criteria using the word "equal" then the select all query
> >> >> > results
> >> >> > are
> >> >> > not being displayed. Does anyone have any clues?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

L
Wed Mar 09 13:07:04 CST 2005

Here is my dropdown list:
<option value= "*" >Select All</option>
<option value="Alabama">Alabama</option>
<option value="Alaska">Alaska</option>
<option value="Alberta">Alberta</option>
<option value="Arizona">Arizona</option>
<option value="California">California</option>
......

Here is my DRW code:
fp_sQry="SELECT * FROM tblCompany WHERE (ContactState LIKE
'%::ContactState::%')"

The Select All is not functioning.

"Kevin Spencer" wrote:

> > Also, if I use Like then if a user selects Virginia then records
> > associated
> > West Virgina are displayed as well and that makes sense b/c I'm using like
> > and not equal.
>
> I co-authored a book on SQL a couple of years ago. Did you try my
> recommendation?
>
> The LIKE operator is case-insensitive, and allows for the use of wildcards.
> If you don't use any wildcards, it works just like a case-insensitive =
> operator.
>
> Also, read my nessage carefully. I also told you how to handle the SELECT
> ALL case. In case you have difficulty finding it:
>
> >> If you want to search ALL states, you add an option whose value is "*"
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> ..Net Developer
> What You Seek Is What You Get.
>
> "L" <L@discussions.microsoft.com> wrote in message
> news:F49132CC-57D4-4B9B-8C2E-4EA8CA2093D2@microsoft.com...
> > Also, if I use Like then if a user selects Virginia then records
> > associated
> > West Virgina are displayed as well and that makes sense b/c I'm using like
> > and not equal. I need a user to select Virgina and only Virgina shows and
> > for
> > a user to have an option to Select all and view records for all states.
> >
> > "Kevin Spencer" wrote:
> >
> >> Okay, again, the purpose of a drop-down list is to prevent errors from
> >> the
> >> user. This means that any value in the drop-down list should be the exact
> >> state name in the database. If so, what is the need for a wildcard
> >> search?
> >>
> >>
> >> Also, let me analyze your query:
> >>
> >> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> >> '::ContactState::'
> >> AND ContactState LIKE '%::ContactState::%')"
> >>
> >> Translated into English, this means that the ContactState field must have
> >> the exact state name in it, AND it should contain the name. Of course, if
> >> it
> >> has the exact state name, it DOES contain the name, so this is redundant.
> >> It
> >> should read:
> >>
> >> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
> >> '::ContactState::' "
> >>
> >> If you want to search ALL states, you add an option whose value is "*"
> >>
> >> --
> >> HTH,
> >>
> >> Kevin Spencer
> >> Microsoft MVP
> >> ..Net Developer
> >> What You Seek Is What You Get.
> >>
> >> "L" <L@discussions.microsoft.com> wrote in message
> >> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> >> >I do have a drop down list with all the states including the option of
> >> > "Select All". But when I use the DRW and use contain, if I select
> >> > Virginia, I
> >> > get Virginia and West Virgina.
> >> >
> >> > Here's the code from DRW:
> >> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> >> > '::ContactState::'
> >> > AND ContactState LIKE '%::ContactState::%')"
> >> > fp_sDefault="ContactState=&ContactState="
> >> >
> >> >
> >> > "Kevin Spencer" wrote:
> >> >
> >> >> Let's have a look at the actual query.
> >> >>
> >> >> The behavior you're describing is correct for a wildcard search. I
> >> >> don't
> >> >> know anything about your requirements, however. If you want to search
> >> >> for
> >> >> a
> >> >> state, how about using a drop-down list box? One of the purposes of a
> >> >> drop-down list box is to prevent user error in typing in search
> >> >> values.
> >> >>
> >> >> --
> >> >> HTH,
> >> >>
> >> >> Kevin Spencer
> >> >> Microsoft MVP
> >> >> ..Net Developer
> >> >> What You Seek Is What You Get.
> >> >>
> >> >> "L" <L@discussions.microsoft.com> wrote in message
> >> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> >> >> >I have used the query designed for the Select All values by using the
> >> >> > wildcard "*" and using the word "contain". The problem that I am
> >> >> > having
> >> >> > now
> >> >> > is that if I select Virginia, records with West Virginia are showing
> >> >> > up.
> >> >> > If I
> >> >> > use the criteria using the word "equal" then the select all query
> >> >> > results
> >> >> > are
> >> >> > not being displayed. Does anyone have any clues?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

L
Wed Mar 09 13:41:08 CST 2005

Once I removed the % from the statement it worked fine.
Thanks Kevin for your help.

"Kevin Spencer" wrote:

> > Also, if I use Like then if a user selects Virginia then records
> > associated
> > West Virgina are displayed as well and that makes sense b/c I'm using like
> > and not equal.
>
> I co-authored a book on SQL a couple of years ago. Did you try my
> recommendation?
>
> The LIKE operator is case-insensitive, and allows for the use of wildcards.
> If you don't use any wildcards, it works just like a case-insensitive =
> operator.
>
> Also, read my nessage carefully. I also told you how to handle the SELECT
> ALL case. In case you have difficulty finding it:
>
> >> If you want to search ALL states, you add an option whose value is "*"
>
> --
> HTH,
>
> Kevin Spencer
> Microsoft MVP
> ..Net Developer
> What You Seek Is What You Get.
>
> "L" <L@discussions.microsoft.com> wrote in message
> news:F49132CC-57D4-4B9B-8C2E-4EA8CA2093D2@microsoft.com...
> > Also, if I use Like then if a user selects Virginia then records
> > associated
> > West Virgina are displayed as well and that makes sense b/c I'm using like
> > and not equal. I need a user to select Virgina and only Virgina shows and
> > for
> > a user to have an option to Select all and view records for all states.
> >
> > "Kevin Spencer" wrote:
> >
> >> Okay, again, the purpose of a drop-down list is to prevent errors from
> >> the
> >> user. This means that any value in the drop-down list should be the exact
> >> state name in the database. If so, what is the need for a wildcard
> >> search?
> >>
> >>
> >> Also, let me analyze your query:
> >>
> >> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> >> '::ContactState::'
> >> AND ContactState LIKE '%::ContactState::%')"
> >>
> >> Translated into English, this means that the ContactState field must have
> >> the exact state name in it, AND it should contain the name. Of course, if
> >> it
> >> has the exact state name, it DOES contain the name, so this is redundant.
> >> It
> >> should read:
> >>
> >> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
> >> '::ContactState::' "
> >>
> >> If you want to search ALL states, you add an option whose value is "*"
> >>
> >> --
> >> HTH,
> >>
> >> Kevin Spencer
> >> Microsoft MVP
> >> ..Net Developer
> >> What You Seek Is What You Get.
> >>
> >> "L" <L@discussions.microsoft.com> wrote in message
> >> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
> >> >I do have a drop down list with all the states including the option of
> >> > "Select All". But when I use the DRW and use contain, if I select
> >> > Virginia, I
> >> > get Virginia and West Virgina.
> >> >
> >> > Here's the code from DRW:
> >> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
> >> > '::ContactState::'
> >> > AND ContactState LIKE '%::ContactState::%')"
> >> > fp_sDefault="ContactState=&ContactState="
> >> >
> >> >
> >> > "Kevin Spencer" wrote:
> >> >
> >> >> Let's have a look at the actual query.
> >> >>
> >> >> The behavior you're describing is correct for a wildcard search. I
> >> >> don't
> >> >> know anything about your requirements, however. If you want to search
> >> >> for
> >> >> a
> >> >> state, how about using a drop-down list box? One of the purposes of a
> >> >> drop-down list box is to prevent user error in typing in search
> >> >> values.
> >> >>
> >> >> --
> >> >> HTH,
> >> >>
> >> >> Kevin Spencer
> >> >> Microsoft MVP
> >> >> ..Net Developer
> >> >> What You Seek Is What You Get.
> >> >>
> >> >> "L" <L@discussions.microsoft.com> wrote in message
> >> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
> >> >> >I have used the query designed for the Select All values by using the
> >> >> > wildcard "*" and using the word "contain". The problem that I am
> >> >> > having
> >> >> > now
> >> >> > is that if I select Virginia, records with West Virginia are showing
> >> >> > up.
> >> >> > If I
> >> >> > use the criteria using the word "equal" then the select all query
> >> >> > results
> >> >> > are
> >> >> > not being displayed. Does anyone have any clues?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Kevin
Wed Mar 09 14:34:40 CST 2005

Ah, that was the wildcard. I didn't realize you were not familiar with the
term. Glad you got it solved!

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
What You Seek Is What You Get.

"L" <L@discussions.microsoft.com> wrote in message
news:9749D615-DE79-4AE8-9628-702052D61913@microsoft.com...
> Once I removed the % from the statement it worked fine.
> Thanks Kevin for your help.
>
> "Kevin Spencer" wrote:
>
>> > Also, if I use Like then if a user selects Virginia then records
>> > associated
>> > West Virgina are displayed as well and that makes sense b/c I'm using
>> > like
>> > and not equal.
>>
>> I co-authored a book on SQL a couple of years ago. Did you try my
>> recommendation?
>>
>> The LIKE operator is case-insensitive, and allows for the use of
>> wildcards.
>> If you don't use any wildcards, it works just like a case-insensitive =
>> operator.
>>
>> Also, read my nessage carefully. I also told you how to handle the SELECT
>> ALL case. In case you have difficulty finding it:
>>
>> >> If you want to search ALL states, you add an option whose value is "*"
>>
>> --
>> HTH,
>>
>> Kevin Spencer
>> Microsoft MVP
>> ..Net Developer
>> What You Seek Is What You Get.
>>
>> "L" <L@discussions.microsoft.com> wrote in message
>> news:F49132CC-57D4-4B9B-8C2E-4EA8CA2093D2@microsoft.com...
>> > Also, if I use Like then if a user selects Virginia then records
>> > associated
>> > West Virgina are displayed as well and that makes sense b/c I'm using
>> > like
>> > and not equal. I need a user to select Virgina and only Virgina shows
>> > and
>> > for
>> > a user to have an option to Select all and view records for all states.
>> >
>> > "Kevin Spencer" wrote:
>> >
>> >> Okay, again, the purpose of a drop-down list is to prevent errors from
>> >> the
>> >> user. This means that any value in the drop-down list should be the
>> >> exact
>> >> state name in the database. If so, what is the need for a wildcard
>> >> search?
>> >>
>> >>
>> >> Also, let me analyze your query:
>> >>
>> >> fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
>> >> '::ContactState::'
>> >> AND ContactState LIKE '%::ContactState::%')"
>> >>
>> >> Translated into English, this means that the ContactState field must
>> >> have
>> >> the exact state name in it, AND it should contain the name. Of course,
>> >> if
>> >> it
>> >> has the exact state name, it DOES contain the name, so this is
>> >> redundant.
>> >> It
>> >> should read:
>> >>
>> >> fp_sQry="SELECT * FROM tblCompany WHERE ContactState LIKE
>> >> '::ContactState::' "
>> >>
>> >> If you want to search ALL states, you add an option whose value is "*"
>> >>
>> >> --
>> >> HTH,
>> >>
>> >> Kevin Spencer
>> >> Microsoft MVP
>> >> ..Net Developer
>> >> What You Seek Is What You Get.
>> >>
>> >> "L" <L@discussions.microsoft.com> wrote in message
>> >> news:7736EF92-8FC5-42B9-84B1-92D1A68FFAEC@microsoft.com...
>> >> >I do have a drop down list with all the states including the option
>> >> >of
>> >> > "Select All". But when I use the DRW and use contain, if I select
>> >> > Virginia, I
>> >> > get Virginia and West Virgina.
>> >> >
>> >> > Here's the code from DRW:
>> >> > fp_sQry="SELECT * FROM tblCompany WHERE (ContactState =
>> >> > '::ContactState::'
>> >> > AND ContactState LIKE '%::ContactState::%')"
>> >> > fp_sDefault="ContactState=&ContactState="
>> >> >
>> >> >
>> >> > "Kevin Spencer" wrote:
>> >> >
>> >> >> Let's have a look at the actual query.
>> >> >>
>> >> >> The behavior you're describing is correct for a wildcard search. I
>> >> >> don't
>> >> >> know anything about your requirements, however. If you want to
>> >> >> search
>> >> >> for
>> >> >> a
>> >> >> state, how about using a drop-down list box? One of the purposes of
>> >> >> a
>> >> >> drop-down list box is to prevent user error in typing in search
>> >> >> values.
>> >> >>
>> >> >> --
>> >> >> HTH,
>> >> >>
>> >> >> Kevin Spencer
>> >> >> Microsoft MVP
>> >> >> ..Net Developer
>> >> >> What You Seek Is What You Get.
>> >> >>
>> >> >> "L" <L@discussions.microsoft.com> wrote in message
>> >> >> news:4F1293A4-AB9B-4758-9AD2-097AA9455272@microsoft.com...
>> >> >> >I have used the query designed for the Select All values by using
>> >> >> >the
>> >> >> > wildcard "*" and using the word "contain". The problem that I am
>> >> >> > having
>> >> >> > now
>> >> >> > is that if I select Virginia, records with West Virginia are
>> >> >> > showing
>> >> >> > up.
>> >> >> > If I
>> >> >> > use the criteria using the word "equal" then the select all query
>> >> >> > results
>> >> >> > are
>> >> >> > not being displayed. Does anyone have any clues?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>