Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    no duplicates index (2002)

    Is it possible to modify the "no duplicates" setting for an index such that "service" and "service" are disallowed as duplicate records, but that "service" and "SERVICE" (or any other difference in upper/lower case) are allowed as distinct entries?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

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

    Re: no duplicates index (2002)

    As far as I can tell, you can't do anything in a regular Jet database. You might try changing the Tools/Options setting for SQL-92 compatibility and see if that will let you do it. I believe you can do it in a SQL Server back-end if you tinker with the database options there.
    Wendell

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

    Re: no duplicates index (2002)

    Access is not case sensitive. You can force case sensitive evaluations in code, but not in the data structures. It really doesn't make sense to do it any other way, since upper case is formatting and you can display it any way you want, regardless of the underlying data.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates index (2002)

    In general, I agree with your point that case is merely formatting. However, for the current situation I am dealing with, I am working with a database created via a third-party software, and case is significant. Therefore, my source data *does* contain "service" and "SERVICE", and I was trying to put records into my own structure that respected the fact that both entries (among others) do actually exist.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates index (2002)

    Thanks for the info and tip. SQL-92 setting did not make any difference.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: no duplicates index (2002)

    You could generate the hexadecimal equivalent of the strings in a query. They will be different for upper case and lower case strings. Here is a function to generate the hex code:

    Function HexString(varInput As Variant) As Variant
    Dim i As Integer
    Dim strResult As String
    If IsNull(varInput) Then
    HexString = Null
    Else
    For i = 1 To Len(varInput)
    strResult = strResult & Right("0" & Hex(Asc(Mid(varInput, i, 1))), 2)
    Next i
    HexString = strResult
    End If
    End Function

    Note: this function will not work correctly if you have unicode characters with numeric value above 255.

Posting Permissions

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