Friday, June 06, 2008

Access 2003 and bugs with special characters and escape sequences

Microsoft Access has some curious behaviors with certain characters. They can be very hard to reference in searches or text operations. Most software (ex. grep) uses “escape sequences” or delimiters for characters like #-()[]?* etc that may have special meaning, but Access struggles here.

From the Access Help files (which are impressive, but often like reading the scrolls of a long lost civilization), we can see that even the expected behavior is very obscure:

  • Wildcard characters are meant to be used with text data types, although you can sometimes use them successfully with other data types, such as dates, if you don't change the Regional Settings properties for these data types.
  • When using wildcard characters to search for an asterisk (*), question mark (?), number sign (#), opening bracket ([), or hyphen (-), you must enclose the item you're searching for in brackets. For example, to search for a
    question mark, type [?] in the Find dialog box.
  • If you're searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets. (However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point.)
  • If you're searching for an exclamation point (!) or closing bracket (]), you don't need to enclose it in brackets.
  • You can't search for the opening and closing brackets ([ ]) together because Microsoft Access interprets this combination as a zero-length string. You must enclose the opening and closing brackets in brackets ([[ ]]).
  • A Microsoft Access project uses different wildcard characters than an Access database uses. For more information, search the Microsoft SQL Server Books Online index for "wildcard characters"…

I love the last bit, where Microsoft hints that even the odd rules Access once used won’t necessarily keep working.

As of June 2008 though things are worse in a patched version of Access 2003. I couldn’t get search on a [ character to work at all, and I had to escape a single quote using: """".

I think software ages at dog speeds, and Access is well into senescence.

No comments: