Chinese - Simplified   Dutch   English - United States   French   German   Greek   Portuguese - Iberian   Spanish  


Usenet Posting #1 - Trigeminal Software, Inc. (English)

Subject: INFO: Split, Filter, Replace, Join, InStrRev in Access 2000
(Originally posted 7/6/99)
I had meant to post this one before, I suppose to answer a question is as good of a time as any....

Split, Filter, Replace, Join, InstrRev

All five of these functions are new in VBA6, and they all include a "Compare" argument which instructs VBA how to handle strings when they need to compare them. It takes one of five things:

**vbUseCompareOption -- Take the Option Compare setting of the module it is in (the default if none is specified, which in Access is usually Option Compare Database, which means use the equivalent of vbDatabaseCompare)

**vbTextCompare -- Take the default LCID of the machine you are running on

**vbDatabaseCompare -- Take the Jet database's sort order (Access only)

**vbBinaryCompare -- do binary comparisons, byte for byte

**An LCID -- any language's LCID, just fill it in and if your machine supports the language, you are in business. For example, if your machine supports Japanese, you can use 1041.

Now, there is a problem (bug) in the implementation of these five new functions that causes them to mishandle the "Comparison" param when you pass vbDatabaseCompare or vbUseCompareOption when the module says "Option Compare Database".... basically, it pretends it is an LCID. Since the comparison value is 2, and it just so happens that 2 is the same as LANGBULGARIAN/SUBLANGNEUTRAL.

Thus, in Access 2000, when you use these functions with the default of no compare argument in an "Option Compare Database" module, it will attempt to perform string comparisons with Bulgarian!

It just so happens that if you use Win95 or Win98, there is no bulgarian support built in on the machine in most cases. This will cause any of these functions to return runtime error 5 (invalid procedure call or argument).

After reboot, since setup did add lang support, the functions will not error out. However, you will still be using Bulgarian for string comparisons, which is not such a great thing, and can be downright awful in any non-US English database as Greek or Japanese with Bulgarian string compares can return the wrong values.

The moral of the story? In Access 2000, *always* provide a Compare argument (do not take defaults!), and never let it be the equivalent of the vbDatabaseCompare option. Your data will thank you for it!

Back to Usenet Musings

Problems with this site? Please contact the
with your comments, questions, or suggestions.