Need coding for filter query... HELP. Been looking no success 1

[Phideaux]

I'm Using MS ACCESS 2000 and I need to retrieve records having a descriprion field containing various words. It is a parts description field named "Descriprion"

I've tried to insert variations of "LIKE" as a search perameter but that only works to a limited degree and won't work in the application that's needed.

I want to have a input box drop down when I run the query. I need to enter either the first letter of a word or the word or partial word into that box and retrieve all of the records containing words having that first letter or the entered word.

my data base is simple but it contains over 50,000 records. The users are not extremely computer friendly.

Due to thier computer fears typing in search strings will not likely work too well.

I can't find any FAQs on this subject so far. any ideas??

thanks in advance for the help.


Phideaux

The mind is the best toy.
Play with someone else's often

 

[r937]

[tt]select * from yourtable
where Descriprion like 'a*'
or Descriprion like 'foo*'[/tt]

please describe what you mean by this --

I've tried to insert variations of "LIKE"
as a search perameter but that only works
to a limited degree


rudy

http://r937.com/

 

[Phideaux]

in queries it is a search perameter when used as a criteris that is where i've been trying to use it.
ex: Like "C*" or like "C" the first retrieves all fields with words starting with "C" and the second retrieves all words containg "C"

Phideaux

The mind is the best toy.
Play with someone else's often

 

[r937]

no, LIKE 'C' has no wildcards, so it retrieves all rows which are equal exactly to 'C'

what is your problem?

 

[Phideaux]

using this format nothing comes back??? it's not that i can't open a query and retrieve the information. note that i need to have an input perameter box pop up on the opening of the query and then enter the "c" or other word inforamtion into that input box.

Phideaux

The mind is the best toy.
Play with someone else's often

 

[r937]

[tt] select * from yourtable
where Descriprion like [enter string] + '*'[/tt]


rudy

 

[Phideaux]

THANKS, it works fantastico !!!!!!!!!!!!!!!!!
you got many stars on this one!!!!!!

Again, Thanks.....
Wayne

Phideaux

The mind is the best toy.
Play with someone else's often

 

[Phideaux]

This works really good! I've encountered one item that can give me the crowning touch on it.

What would the syntax be to ignore a blank or to pick up the key search words no matter where they fall in the field?? That'd be the absolute best situation. Thanks for all of your help and sticking with me on this problem.... Wayne

Phideaux

The mind is the best toy.
Play with someone else's often

 

[r937]

words? as in, more than one?

now you are asking for something much more sophisticated

do you want to find one word, or the other, or both? and do they have to be in that order? and how is the user going to specify more than one word? will the user know if the words will be joined with ORs or ANDs?

see Can one group LIKEs together?

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid565521_tax285649,00.html

rudy

 

[Phideaux]

i'm going to look at the reference you indicated.. the field contains a simple explanation of the part description in a parts inventory DB. The order of listing the parts might be "Bearing for Toyota Camry" from one data entry source and from another it may show up as "Toyota, front input bearing, Camry" there may even be blank spaces at the front of the field.

Thanks for the quick response, Wayne

Phideaux

The mind is the best toy.
Play with someone else's often