distinguish upper/lower case in unique index?

  • Thread starter Thread starter michael brooks
  • Start date Start date
M

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]
 
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
 
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.
 
Back
Top