If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Case sensitivity & Primary key
"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 |
#3
|
|||
|
|||
Case sensitivity & Primary key
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 -- |
#4
|
|||
|
|||
Case sensitivity & Primary key
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 -- |
#5
|
|||
|
|||
Case sensitivity & Primary key
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 -- |
#6
|
|||
|
|||
Case sensitivity & Primary key
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 -- |
Thread Tools | |
Display Modes | |
|
|