DB QUERY

[Ted Ljong]

Hi

Im using FP 2K and Acess2000 and I have this db query

SELECT * FROM medlemsregister WHERE (fnamn LIKE '::fnamn::%') ORDER BY enamn
ASC,fnamn ASC

It works ok when i wright any letter in the formfield but when I leave it
empty and hit enter the entire db is listed. I don´t want anything to be
listed if I don´t search for anything in that field. I hope there is someone
that can give me a clue how to wright the query.

Thanks

Ted Ljong

 

[Kevin Spencer]

Hi Ted,

Think about it. You're using a wildcard query. The "%" indicates that
whatever the first part is, followed by ANYTHING or NOTHING, is a mtach.
Therefore, if the first part is a blank string, any value in the column
which begins with nothing followed by ANYTHING or NOTHING is a match. In
other words, ALL RECORDS match.

to prevent this, you could use form validation to require at least one
character in the "fnamn" field.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Neither a follower nor a lender be.

 

[Ted Ljong]

Kevin
It just don´t seem to be my day today I just posted the answer directly to
your mail by misstake I try again to post it to the newsgroup, sorry.

Ok I think I know what you mean now but if do so the visitor has to wright
something in that field. My form contains several fields like a filter, if
you fill in one of the fields you get many hits but if you fill in several
fields you get less hits. I want to give the visitors a free choice of what
filds they want to fill in.

Ex
Search for name and city and country and yyyyy
or
Search for name and country
or
Search for name

It can be several types of search, more than I described here.

And I want to do all this in the same form with one search button

Ted

 

[Kevin Spencer]

Well, Ted, you have several options:

1. Edit your JavaScript form validation script to require at least one
character in the "fnamn" field ONLY if all other fields are empty.
2. Change the logic of your query to not filter on "fnamn" if it is blank
3. Add ASP logic to the code to put in a non-possible "fnamn" value (such as
one that is longer than the field) when the "fnamn" form value is left
blank, so that it returns no records for "fnamn"

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Neither a follower nor a lender be.

 

[Kevin Spencer]

If you tell me what database you're using, I might be able to help with the
second option.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Neither a follower nor a lender be.

 

[Ted Ljong]

I am using MS Access 2000, I really appreciate if you will do that
Ted

 

[Kevin Spencer]

I believe you can do this with an IIF statement in your query. Example:

SELECT * FROM medlemsregister WHERE fnamn LIKE IIF('::fnamn::' = '', '',
'::fnamn::%')

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Neither a follower nor a lender be.

 

[Ted Ljong]

Than ks Kevin for taking your time.
Maybe this is the answer At the moment I'm trying to insert your query in a
query with three fields: fnamn, enamn and spec. All three shall be
searcheable one at the time or two together or all three together and blank
fields shall not make a hit. I tell you later if it works.
Ted

 

[Kevin Spencer]

Yes, you can other conditions as well.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
http://www.takempis.com
Neither a follower nor a lender be.