Replace Function 'Compare' parameter

[Peter Hibbs]

Hi All

I have used the incredibly useful 'Replace' function in Access for
many years but have never understood the 'vbDatabaseCompare'
parameter. Access Help is singulary unhelpful as it just says -

"vbDatabaseCompare 2
Microsoft Access only. Performs a comparison based on information in
your database."

Does anyone know exactly what this parameter does, when would you use
it and is it of any use in practice. Maybe some code examples would be
useful.

Peter Hibbs.

 

[Allen Browne]

The Database compare depends on the locale settings. Different languages
give different results for comparisons and sorting (e.g. accented
characters.) vbDatabaseCompare uses the locale settings rather than a
straight Text (e.g. case-insensitive) or Binary (case sensitive, equal at
the bit level) comparison.

 

[John W. Vinson]

I have used the incredibly useful 'Replace' function in Access for
many years but have never understood the 'vbDatabaseCompare'
parameter. Access Help is singulary unhelpful as it just says -

"vbDatabaseCompare 2
Microsoft Access only. Performs a comparison based on information in
your database."

Does anyone know exactly what this parameter does, when would you use
it and is it of any use in practice. Maybe some code examples would be
useful.

vbDatabaseCompare uses the current setting of the database's string
comparison operator. Normally this will use the computer's language
setting to handle (e.g.) accented characters, and will be case
insensitive. The alternative vbBinaryCompare will compare the exact
ASCII representation of the character. For example:

?Instr(1,"XYZabcxZ","x",vbDatabaseCompare)
1

?Instr(1,"XYZabcxZ","x",vbBinaryCompare)
7

The first example finds an x (or X) in the first position of the
string, since it's not case sensitive; the second example requires an
exact match, so it finds the lower-case x in the 7th position.

In the Replace() function this would let you choose between replacing
text in a case-sensitive or case-insensitive manner.


John W. Vinson [MVP]

 

[Peter Hibbs]

Thanks Allen and John. I will try it out with that info.