distinguish upper/lower case in unique index?

[michael brooks]

Dear Wizards,

I'm using Access 2000...and so far haven't been able to find a solution on
the Web.

I have tables with mostly English data, but some fields with transliterated
foreign words. The transliteration distinguishes upper/lower case. That is,
"D" is a different letter from "d", etc.

In my VB modules, I set "Option compare binary", and there is no problem,
though I haven't tested "find" commands.

BUT
1. I would like to set a unique index on some of these fields. Access
complains if one record is "tha" and another is "Tha".
Any simple way around this?
Maybe I could create a calculated field which converts the actual data to a
unique form and then index that...but wonder if there is an easier
way--can't find any database or table properties that seem to do this.

2. I also notice that if I
Select * from SomeTable where SomeField = "Tha"
then I also get "tha". I'd like the select to be based on binary
comparison, i.e. case-sensitive.
Thanks for your insights!
[p.s. Please post replies to newsgroup thread]

 

[Allen Browne]

JET (the data engine in Access) is case-insensitive, so my suggestion would
be to use a different back end that can handle case-sensitive data.

If that's not practical, it is possible to design queries where you call VBA
using StrComp instead of =, e.g.:
WHERE StrComp(Table1.Code, Table2.Code, 0) = 0

 

[Tom van Stiphout]

On Sun, 1 Feb 2009 15:18:28 -0600, "michael brooks" <[email protected]>
wrote:

I doubt that Access (better: Jet) can create a case-sensitive
database. Here is a work-around:
http://msdn.microsoft.com/en-us/library/bb258147.aspx
SQL Server, including the free Express edition, *does* support a
binary sort order.

-Tom.
Microsoft Access MVP

 

[david]

If you need binary indexing, referential integrity and joins,
use DDL to create a binary text field, as described in this
MS KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244693

Do you need to use transliterated characters? An alternative not
discussed in that article might be to create a database in the
correct language?

(david)

 

[michael brooks]

Thank you, Allen, Tom, David, and any further posters for your inputs...very
useful and much appreciated. ALL of your thoughts increased my understanding
of what's happening and possible ways to deal with it.