Keep in mind that the concept of a database is finite, concise pieces of
information. If there is important data, that is needed for searches or
sorts, then it should be in its own dedicated field, and utilize a lookup
table when possible. [/soapbox]
There are pattern matching operators, but finding mispellings is next to
impossible.
Truck
Turck
Truch
Trcuk
Trukc
Here is a blurb from the help file:
Using Wildcard Characters in String Comparisons
Built-in pattern matching provides a versatile tool for making string
comparisons. The following table shows the wildcard characters you can use
with the Like operator and the number of digits or strings they match.
Character(s) in pattern Matches in expression
? or _ (underscore) Any single character
* or % Zero or more characters
# Any single digit (0- 9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist
You can use a group of one or more characters (charlist) enclosed in
brackets ([ ]) to match any single character in expression, and charlist can
include almost any characters in the ANSI character set, including digits.
You can use the special characters opening bracket ([ ), question mark (?),
number sign (#), and asterisk (*) to match themselves directly only if
enclosed in brackets. You cannot use the closing bracket ( ]) within a group
to match itself, but you can use it outside a group as an individual
character.
In addition to a simple list of characters enclosed in brackets, charlist
can specify a range of characters by using a hyphen (-) to separate the
upper and lower bounds of the range. For example, using [A-Z] in pattern
results in a match if the corresponding character position in expression
contains any of the uppercase letters in the range A through Z. You can
include multiple ranges within the brackets without delimiting the ranges.
For example, [a-zA-Z0-9] matches any alphanumeric character.
It is important to note that the ANSI SQL wildcards (%) and (_) are only
available with Microsoft® Jet version 4.X and the Microsoft OLE DB Provider
for Jet. They will be treated as literals if used through Microsoft Access
or DAO.
Other important rules for pattern matching include the following:
a.. An exclamation mark (!) at the beginning of charlist means that a
match is made if any character except those in charlist are found in
expression. When used outside brackets, the exclamation mark matches itself.
b.. You can use the hyphen (-) either at the beginning (after an
exclamation mark if one is used) or at the end of charlist to match itself.
In any other location, the hyphen identifies a range of ANSI characters.
c.. When you specify a range of characters, the characters must appear in
ascending sort order (A-Z or 0-100). [A-Z] is a valid pattern, but [Z-A] is
not.
d.. The character sequence [ ] is ignored; it is considered to be a
zero-length string ("").
HTH,
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips:
http://www.fmsinc.com/free/tips.html
polisci grad said:
Is it possible to have a more functional keyword search that returns
results
based on the degree of similarity to the word or phrase typed in a text
box??? As of right now, I have a wildcard search (Like * & """" & *) but
this will not return anything if a) the word is typed in wrong, b) a
phrase
is used....
My query is performed on a string created of all the text fields in a
table
that describe the record. I'm afraid that the only possible way to do
this
is to break the parametre entered by the user into individual characters,
then run a wildcard query as above on successive combinations of
individual
letters (ie- 'Truck' becomes T, R, U, C, K, and the query searches for T,
TR,
TRU, TRUC, TRUCK) This might work, but I wouldn't know how to do it.
Perhaps there is an easier way?
