Access 2003 driving me mad!!!

[R.T.JOHNSON]

Hi - really "new bloke - knows nothing" question so apologies before I
start..........

All i'm trying to do is run a query in Access 2003 that returns a list
of records containing the word "Pike" from a field called "Title".

Sounds simple but no records are returned - despite numerous records
exsting which meet this criteria.

I've tried changing the query to "*pike*" but then I get records
returned containing things like "Spike Lee".

All I want to do is find any records containg the word "pike" not any
record containg a word which might include the letter "p", "i", "k"
and "e".

Please help before I go mad!!!!

 

[Tom Lake]

Hi - really "new bloke - knows nothing" question so apologies before I
start..........

All i'm trying to do is run a query in Access 2003 that returns a list
of records containing the word "Pike" from a field called "Title".

Sounds simple but no records are returned - despite numerous records
exsting which meet this criteria.

I've tried changing the query to "*pike*" but then I get records
returned containing things like "Spike Lee".

All I want to do is find any records containg the word "pike" not any
record containg a word which might include the letter "p", "i", "k"
and "e".

Add a space before and after:

Like "* Pike *"

Tom Lake

 

[Allen Browne]

Actually, Tom, it's not quite that simple.

If the first word is Pike, there's no preceeding space.
If a sentence ends with Pike, there is no trailng space.
Or, it could be a question mark, exclamation point, ... any other
punctation.
Or it could be the first word of a paragraph, where the previous character
is Chr(10).
Or it might have dashes around it.
Or brackets.
Or square brackets or braces.
Or ...

 

[John Nurick]

Actually, Tom, it's not quite that simple.

....except with a regular expression, e.g.

WHERE rgxValidate([Title], "\bPike\b", True, False)

(rgxValidate is at http://www.mvps.org/access/modules/mdl0063.htm )

 

[John W. Vinson]

Hi - really "new bloke - knows nothing" question so apologies before I
start..........

All i'm trying to do is run a query in Access 2003 that returns a list
of records containing the word "Pike" from a field called "Title".

As noted elsethread, it's not all that simple, but you can get a bit
better results using a threefold criterion:

LIKE "* pike *" OR LIKE "pike *" OR LIKE "* pike"

to catch the word delimited by blanks, in the middle, beginning or end
of the title. It won't catch the title "Pike" however, since that
doesn't have a blank; nor will it catch "Pike's Peak" or "Get yourself
down the pike!" because of the punctuation mark.

John W. Vinson [MVP]

 

[Polecat]

As noted elsethread, it's not all that simple, but you can get a bit
better results using a threefold criterion:

LIKE "* pike *" OR LIKE "pike *" OR LIKE "* pike"

to catch the word delimited by blanks, in the middle, beginning or end
of the title. It won't catch the title "Pike" however, since that
doesn't have a blank; nor will it catch "Pike's Peak" or "Get yourself
down the pike!" because of the punctuation mark.

John W. Vinson [MVP]

Thanks guys. I'm amazed that this is so difficult. Is there really
not a simple way to find a specific word within a sentence?

 

[Rick Brandt]

Thanks guys. I'm amazed that this is so difficult. Is there really
not a simple way to find a specific word within a sentence?

What John gave you is pretty simple. Most simpler solutions get by with the
*pike* syntax and just live with the fact that you will get more rows back than
you wanted. In most cases having all the rows you wanted (plus a few more) is
better than a solution that leaves some desired rows out. To be more exact
requires a bit less simplicity.

 

[John W. Vinson]

Thanks guys. I'm amazed that this is so difficult. Is there really
not a simple way to find a specific word within a sentence?

Access is a database - as such, the assumption is made that fields are
"atomic", and that it's not typically necessary to search for PORTIONS
of a field. There are other computer programs - "textbases" - which
are in fact designed to search for keywords (KWIC indexes - "Key Word
In Context" are an example) within free text. It's a really rather
different paradigm and rather more complex software.

I haven't used them, but I understand that both SQL/Server and ORACLE
have addons which provide full text searching capability. Access is
more limited, and what I've posted is about the best it can do; it's
simply a different task than the program was designed to do.

John W. Vinson [MVP]

 

[Polecat]

Access is a database - as such, the assumption is made that fields are
"atomic", and that it's not typically necessary to search for PORTIONS
of a field. There are other computer programs - "textbases" - which
are in fact designed to search for keywords (KWIC indexes - "Key Word
In Context" are an example) within free text. It's a really rather
different paradigm and rather more complex software.

I haven't used them, but I understand that both SQL/Server and ORACLE
have addons which provide full text searching capability. Access is
more limited, and what I've posted is about the best it can do; it's
simply a different task than the program was designed to do.

John W. Vinson [MVP]

Well thanks John. Actually, the suggested query works well enough.
As Rick said - it's better to get the odd irrelevent return rather
than missing too many records.

 

[John W. Vinson]

Well thanks John. Actually, the suggested query works well enough.
As Rick said - it's better to get the odd irrelevent return rather
than missing too many records.

I've used some textbase software (now no longer available, this was
fifteen years ago) and it really had some neat features - like being
able to search for two, three or more words in the same sentence or
the same paragraph or the same "chapter"; automatic part of speech
extraction (e.g. searching for "speak" finds "spoke" and "spoken" as
well, and is clever enough to usually NOT pick "the spoke of the
wheel"); automatically calculate word frequencies... on and on. If you
think Access has a steep learning curve...!!!

John W. Vinson [MVP]