Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Case sensitive data (Access 2000 SR1)

    Not a problem I usually need to worry about, but ... I'm dealing with some case-sensitive data at present (i.e. fields containing codes identical bar case e.g. C100a and C100A) - is there any way of telling Access that these are different so that e.g. a primary key can be set on this, and joins properly implemented with other similar fields, or is this just not possible in Access?

    Can SQL Server handle case sensitive fields - I have the option of transferring the data to that environment?

    Thanks

    Jeremy

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Case sensitive data (Access 2000 SR1)

    At the data level, Access is case-insensitive, so no, you won't be able to set a primary key on that field. I can't speak to SQL Server, which isn't running on my machine at the moment, but I suspect the same thing holds true there. One workaround would be to create an additional field and populate it with the ASCII value of the field in question. Capital and lower case letters have different ASCII values, so you could use that field as a primary key. You would then need to propagate those ASCII values as foreign keys to related tables and make your joins on those fields or else use a calculated expression in your query to render the ASCII values on the fly and match them between the tables. The latter approach will result in slower queries though.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Case sensitive data (Access 2000 SR1)

    I don't think there is a way in Access (without resorting to tricks) to do what you ask. SQL Server does have the ability to sort that data logically, as does Access, but I'm not sure it will create a unique index that way either. If your field is relatively short, you could trick either system by storing it as a number rather than a string, but it would be limited to 8 bytes (unless you want to try messing with GUIDs).
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •