Query

[L]

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 Spencer]

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]

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="

 

[Stefan B Rusynko]

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
_____________________________________________


|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.
| >
| > | > >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]

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

 

[L]

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.

 

[Kevin Spencer]

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]

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.

 

[L]

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]

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:

--
HTH,

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

 

[L]

Kevin,

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

 

[L]

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.

 

[L]

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

 

[Kevin Spencer]

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.