Tuesday, February 12, 2008

MSACCESS: Searching for Wildcards

Still end up using Microsoft Access alot when manipulating data. Since it uses the old Microsoft wildcard characters, I often forget how to search for the wildcards themselves. Found a good article that summarizes how to do just that:

Search stringMatch list settingResults
[*]Any Part of FieldReturns all records that contain an asterisk (*). This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).
 Whole FieldReturns records that consist only of an asterisk.
 Start of FieldReturns records that start with an asterisk.
*[*]*Any Part of FieldReturns all records that contain an asterisk (*) and any surrounding text. This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).
 Whole FieldSame result.
 Start of FieldSame result.
[!*]Any Part of FieldReturns all records that do not contain an asterisk. Keep in mind that this search pattern can return every letter of every word in a record when you use this setting in the Match list. This syntax also works for question marks (?), number signs (#), opening brackets ([), and hyphens (-).

Note  The search string *[!*]* will return records that contain asterisks because it finds all the text that surrounds the asterisk.

 Whole FieldReturns no results at all.
 Start of FieldReturns the first letter of any record that does not contain an asterisk.
ma*[ch]Any Part of FieldReturns all records that contain "ma" and either "c" or "h". For example, this string returns "march" and "match", and it also returns "math" and "manic".
 Whole FieldReturns all records that start with "ma" and end with either "c" or "h". For example, this string returns "march" and "match", and it also returns "math" and "manic".
 Start of FieldReturns all records that start with "ma" and contain "c" or "h".
ma*[!ch]Any Part of FieldHighlights the letters "m" and "a" and all text that follows those letters until it encounters a "c" or an "h". The following figures illustrate this.

| March |

| Match |

In other words, even though you're trying to exclude records that contain "c" and "h", you may see those records because Any Part of Field matches the text that precedes the brackets.

 Whole FieldReturns all records that do not contain a "c" or an "h" if those records end in "c" or "h". For example, the find operation does not return "manic" because the word ends with a "c", but it does return "maniacal" because characters follow the "c".
 Start of FieldReturns those records that start with "ma". Again, Access matches any text that precedes the characters enclosed in brackets, so you may see unwanted results.

No comments: