case insensitive

[jnikho]

I have created a table with 2 columns one being a primary key of varchar(1).
I successfully insert a record with a value of 'A' but I am not able to insert another row with a value of lower case 'a', I get the following error:

RESULT: Error - The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Is there a way to allow both upper and lower case character be inserted in my example?

[missinglinq]

As you discovered, Access is case insensitive! I can't really imagine a practical way to make this work, nor, to be honest, can I imagine why you would want to, especially for a Primary Key! The whole point of a PK is for it to be absolutely unique! If you worked some kind of hack using ACSII values, you'd have to differentiate between the two (and presumably do this for all other letters) in every aspect of the database! And how would you ever be sure whether the user meant A or a when the letter was entered? I can't conceive of any validation that would work.

Welcome to TheScripts!

Linq ;0)>

[ADezii]

I have created a table with 2 columns one being a primary key of varchar(1).
I successfully insert a record with a value of 'A' but I am not able to insert another row with a value of lower case 'a', I get the following error:

RESULT: Error - The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Is there a way to allow both upper and lower case character be inserted in my example?

Is there a way to allow both upper and lower case character be inserted in my example?

Everything that linq stated is solid advice, but if for some odd reason you needed a 1 Alpha Character Field and need to differentiate between 'A' and 'a', you could always store the ASCII Character of the Field (A = 65 and a = 97), and make it the Primary Key Field. Of course you would have to handle the conversions to and from the Table.

[jnikho]

Thank you missinglinq and ADezii for your posts.

I actually do need to differentiate between lower and upper case letters as per business requirement. The ASCII representation sounds like a good solution.

Thanks again.