Query Criteria: Too Strict (way too strict!!!)

[BiggyRat]

Hello Again...

In my company's database...which keeps architectural info & conditions for a homeowners' association...I need to run queries from time to time...that do not have a strict criteria definition.

My inspector in the field, for example, enters info on 'Satellite Dishes'...but there will always be different circumstances as to condition...meaning that the entered language always differs.

So...Access queries won't show results unless the specific phrase is found.

I need to be able to loosen up those criteria boundaries. I want to be able to run a query; & specify say, "Satellite Dish" as the criteria...& then get results on every entry that has the words 'satellite dish' mentioned in the records. Can this be done???

Thanks,


Biggy Rat
Access 2003

 

[Remou]

You can use Like with wild cards (DAO=*,ADO=%):

Like "*Satellite*

 

[Golom]

Something like

Where SomeField LIKE '*Satellite Dish*'

?

 

[SkipVought]

and if there might be some entries like...
[tt]
...satellite dish...
or
...satellite Dish...
[/tt]
you might...

Where UCase(SomeField) LIKE '*SATELLITE DISH*'

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

...in fact, I'd test this...

Where UCase(SomeField) LIKE '*DISH*'

and see what results you come up with.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Remou]

I have always found Access to be insensitive to case.

 

[GingerR]

me too

I've written a keyword search at my company in my db, that will search on:

EXACT PHRASE: Satellite Dish
ALL WORDS: Satellite and Dish in the phrase but not exact
ANY WORDS: Satellite or Dish

and spits out the results in that order: Exact Phrase first, then ALL, then ANY. It's fabulous if I do say so myself

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BiggyRat]

Thanks very much, everyone...

Quick question:

These code entries...they get plugged into the Criteria box...as per normal, right?

Also...Ginger...is what's written here your code...or is it different?

Thanks again,

BR

 

[BiggyRat]

UPDATE:

Reason I'm asking is...I plugged those code entries into the Criteria box...& was informed by the program that they're all Invalid Syntax

BR

 

[Remou]

Please post your SQL.

 

[GingerR]

To do the big keyword search involves more than just a query.

Two tables: some code that splits your phrase into single words and puts them into one of these tables then finds matches to these words and puts them into the second table.

a few queries: one that matches the exact phrase and one that matches any of the words

a last Union query that puts it all together.

I would put this into a form:
Main form with an unbound text box for your keywords.
A button you hit that runs the code.
A subform that shows the results.

You may want to do that anyhow even if you just use a Like* statement - how is it set up now?


It goes very fast and isn't that complicated, so if you want more details let me know. I might not answer until Monday though because I'm home and my connection is slooowww.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[BiggyRat]

I was placing too much in, actually. I went with just the basic wildcard (Like '*Satellite Dish*') & it worked.

Thanks all,

BiggyRat