Case sensitivity & Primary key

I am using Access 2003. I am not pulling data from any other database.
I have a table with a field that contains records such as 8H7N and 8H7n -
that is they only differ by the case of the letters, "n" in this example.
Unfortunately I have no control over this so I have to try and make it work
with this case sensitivity. To make matters worse I need this field to be a
primary key. I cannot do this at the moment because records like this are
not considered unique.
There are no other fields in this or any other table in the database where
case is an issue.

Is there anyway I can do what I want? I have read about the ability to turn
case sensitivity on & off at the field level but I don't think that that was
in Access.

Many thanks in advance for any help, advice or pointer you can give me.

Peter

"Peter" wrote in message
...
I am using Access 2003. I am not pulling data from any other database.
I have a table with a field that contains records such as 8H7N and 8H7n -
that is they only differ by the case of the letters, "n" in this example.
Unfortunately I have no control over this so I have to try and make it work
with this case sensitivity. To make matters worse I need this field to be a
primary key. I cannot do this at the moment because records like this are
not considered unique.
There are no other fields in this or any other table in the database where
case is an issue.

Is there anyway I can do what I want? I have read about the ability to turn
case sensitivity on & off at the field level but I don't think that that was
in Access.

Many thanks in advance for any help, advice or pointer you can give me.

There are ways to do case sensitive comparisons in Access, but you cannot make
the data at the table level case-sensitive. Certainly not to the degree that
you could make that your Primary Key.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

hi Peter,

Peter wrote:
I have a table with a field that contains records such as 8H7N and 8H7n -
that is they only differ by the case of the letters, "n" in this example.
Unfortunately I have no control over this so I have to try and make it work
with this case sensitivity. To make matters worse I need this field to be a
primary key.
You don't need it to be the primary key, use a surrogat key, e.g. an
autoincrement as primary key.

To keep it unique in the database, you must use a different encoding,
e.g. Hex(). Untested code:

Public Function GetHex(AString As String) As String

GetHex = ""
For Count = 1 To Len(AString)
GetHex = GetHex & Right("00" & Hex$(Mid$(AString, Count, 1)), 2)
Next Count

End Function

Public Function GetStr(AHex As String) As String

GetStr = ""
For Count = 0 To Len(AHex) \ 2
GetStr = GetStr & Chr$(Val("&H" & Mid$(AHex, Count * 2 + 1, 2))
Next Count

End Function


mfG
-- stefan --

Thank you both for such quick replies. I will try your suggestion Stefan.

Thanks again, Peter

"Stefan Hoffmann" wrote:

hi Peter,

Peter wrote:
I have a table with a field that contains records such as 8H7N and 8H7n -
that is they only differ by the case of the letters, "n" in this example.
Unfortunately I have no control over this so I have to try and make it work
with this case sensitivity. To make matters worse I need this field to be a
primary key.
You don't need it to be the primary key, use a surrogat key, e.g. an
autoincrement as primary key.

To keep it unique in the database, you must use a different encoding,
e.g. Hex(). Untested code:

Public Function GetHex(AString As String) As String

GetHex = ""
For Count = 1 To Len(AString)
GetHex = GetHex & Right("00" & Hex$(Mid$(AString, Count, 1)), 2)
Next Count

End Function

Public Function GetStr(AHex As String) As String

GetStr = ""
For Count = 0 To Len(AHex) \ 2
GetStr = GetStr & Chr$(Val("&H" & Mid$(AHex, Count * 2 + 1, 2))
Next Count

End Function


mfG
-- stefan --

If you have the time and budget, Oracle is case sensitive including primary
key constraints..
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Peter" wrote:

Thank you both for such quick replies. I will try your suggestion Stefan.

Thanks again, Peter

"Stefan Hoffmann" wrote:

hi Peter,

Peter wrote:
I have a table with a field that contains records such as 8H7N and 8H7n -
that is they only differ by the case of the letters, "n" in this example.
Unfortunately I have no control over this so I have to try and make it work
with this case sensitivity. To make matters worse I need this field to be a
primary key.
You don't need it to be the primary key, use a surrogat key, e.g. an
autoincrement as primary key.

To keep it unique in the database, you must use a different encoding,
e.g. Hex(). Untested code:

Public Function GetHex(AString As String) As String

GetHex = ""
For Count = 1 To Len(AString)
GetHex = GetHex & Right("00" & Hex$(Mid$(AString, Count, 1)), 2)
Next Count

End Function

Public Function GetStr(AHex As String) As String

GetStr = ""
For Count = 0 To Len(AHex) \ 2
GetStr = GetStr & Chr$(Val("&H" & Mid$(AHex, Count * 2 + 1, 2))
Next Count

End Function


mfG
-- stefan --

silly, question:
where do i type the code?
is it modules?

"Jerry Whittle" wrote:

If you have the time and budget, Oracle is case sensitive including primary
key constraints..
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Peter" wrote:

Thank you both for such quick replies. I will try your suggestion Stefan.

Thanks again, Peter

"Stefan Hoffmann" wrote:

hi Peter,

Peter wrote:
I have a table with a field that contains records such as 8H7N and 8H7n -
that is they only differ by the case of the letters, "n" in this example.
Unfortunately I have no control over this so I have to try and make it work
with this case sensitivity. To make matters worse I need this field to be a
primary key.
You don't need it to be the primary key, use a surrogat key, e.g. an
autoincrement as primary key.

To keep it unique in the database, you must use a different encoding,
e.g. Hex(). Untested code:

Public Function GetHex(AString As String) As String

GetHex = ""
For Count = 1 To Len(AString)
GetHex = GetHex & Right("00" & Hex$(Mid$(AString, Count, 1)), 2)
Next Count

End Function

Public Function GetStr(AHex As String) As String

GetStr = ""
For Count = 0 To Len(AHex) \ 2
GetStr = GetStr & Chr$(Val("&H" & Mid$(AHex, Count * 2 + 1, 2))
Next Count

End Function


mfG
-- stefan --