Comparing cases

[aziz rasul]

I have the following data: -

Field1 Field2
ST16 St16

I want to extract records that have values in the 2 fields that have different case in the data. In the example here we have a "T" in Field1 but a "t" in Field2.

 

[ColinEssex]

Firstly - do you want to change one of them so that the data is uniform?

Secondly - You can put an Input Mask on the field so that whatever is typed in (lowercase or upper) will be converted to what you want.


Col

 

[aziz rasul]

No. I don't want to change the data. Also the data is imported into the db. The task is to obtain a list of records where the data in the fields is different. It will be someone else's task to decide which piece of data is accurate and change the other one.

 

[Calvin]

You'll need to lookup and use the "Option Compare" Statement in the help file to get what you want. But basically you create a New module and at the top where it reads "Option Compare Database" you will need to change this to "Option Compare Binary", then create a function in this module to do your string comparison and all comparisons in the module (this module only) will be compared on a binary level where A<>a, ST16<>St16.
If you leave the default "Option Compare Database" or "Option Compare Text" declared at the begining of the module then A=a and ST16=St16.

Below is a quote of what the help file contains if you can't find it.

Used at module level to declare the default comparison method to use when string data is compared.

Syntax

Option Compare {Binary | Text | Database}

Remarks

If used, the Option Compare statement must appear in a module before any procedures.

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø

Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:

(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.

 

[aziz rasul]

Thanks Calvin.