Queries:
First you must define what you mean by a whole word. That is what are the
characters on either side of a word that decides what a word is.
Sample Text:
Does Angie want to go to Richard's house? Yes!
What's the email address? (e-mail address removed)
My reason (not your business) is that I need to set up an appointment.
Definition one: A word is a string of characters surrounded by a space on
both sides. So in the above sample text the following would not be defined
as whole words.
Does, Richard, house,Yes, What, What's, address, Spencer, somewhere,com,
not, business, my, and appointment
Criteria: Like "* " & [Match What] & " *"
Definition two: A word must have a space or a punctuation character at the
start and the end. In the above sample. The folowing would not be defined
as whole words- What, Spencer, com, My
Criteria: Like "*" & "[ -@[-`{-~]" & [Match what] & "[ -@[-`{-~]" & "*"
Definition three: A word must have a space, a line feed, a carriage return,
a tab character, or a punctuation mark at the beginning and end.
Criteria: Like "*" & "[ -@[-`{-~]" & [Match what] & "[ -@[-`{-~]" & "*"
Definition four: A word must have a space, a line feed, a carriage return,
or a punctuation mark at the beginning and end and must contain at least one
letter.
(Working on criteria for this one)
To make this work you also need to temporarily append a space to the
beginning and end of the field you are searching.
In the query grid, you would make a calculated field to search.
Field: FindWholeWord: " " & [The Field Name] & " "
Criteria: <<one of the criteria statements above>>
Bob Barrows said:
eb1mom said:
In a description field I want to search for an exact word. I am using
Like "*" & [Forms]![frmSearch]![SearchCriteria] & "*"
as criteria. If I search for cat it returns records with the word cat
and also records containing letters cat, (catalog, catamaran, etc).
I have tried lots of variations and can't seem to get correct
criteria. Suggestions would be appreciated. Thank-you
Well, how is a "word" identified? Make a list of all the ways that you
wish to identify a word in a string. Start with:
spaces around the word
This is easy: ... like "* cat *"
punctuation either before or after the word (users don't always remember
to type a space after a comma or period)
Now it gets harder:
... like "* cat *" or ... like "*, cat *" or ... like "*. cat *" or ...
like "*,cat *"
or ... like "* cat, *"
Not good, is it?
This is what Regular Expressions were created for. I don't have time to go
deeply into the subject (you can find plenty of info by googling for it),
but I think you will be able to use them in a query by using a custom VBA
function.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
