none
Unicode version support in Access 2013 RRS feed

  • Question

  • Hello!

    Does anybody know, which version of Unicode is supported by Access 2013 in terms of comparing strings in SQL queries. I work with signs from Latin Extended-D range, which were introduced with version 5.1 of the Unicode Standard. And I experience problems, because Access does not correctly compare strings containing these characters.

    I can exemplify the problem, which I encounter in different situations with the following SQL query:

    SELECT (ChrW(-22729)=ChrW(-22695)) AS Expr1, "a"="b" AS Expr2;

    SELECT (ChrW(42786)=ChrW(42841)) AS Expr1, "a"="b" AS Expr2;
    In my environment (Win 7, Office 2013 32 bit) Expr1 is true, while Expr2 is false. However, Expr1 should have been false, because two different unicode signs A722 and A759 are compared. I get the same results when I compare any other Latin Extended-D signs. However, when I compare a Latin Extended-D sign with a basic Latin sign, the result is false.

    Could someone tell me if the same problem occurs on other versions of Access (in 64-bit or in Access 2016). What would be the best solution (in terms of performance) for running queries and applying filters, which would correctly handle these signs?

    What version of Unicode is officially supported by Access 2013? Unicode 5.1 was officially introduced back in 2008. I believe, Microsoft could have taken steps to support it by now.

    Thanks

    UPD: I corrected the mistake in the SQL example. The outcome is unaffected.

    UPD2: Alternatively the  example may be presented as

    SELECT (ChrW(Val("&Ha722&"))=ChrW(Val("&Ha759&"))) AS Expr1, "a"="b" AS Expr2;

    At any rate the problem is that the database engine thinks that ChrW(Val("&Ha722&"))=ChrW(Val("&Ha759&"))  although the same is not true in VBA. This expression results in True in queries although it results in False in VBA code.


    • Edited by Alex F Ilin Wednesday, August 24, 2016 5:46 PM
    Wednesday, August 24, 2016 4:03 PM

Answers

  • >>>These are exactly the signs I need in my work, but Access cannot properly compare them (falsely returning true for the expression "Ꜣ"="ꝙ" in queries).

    According to your description, I have reproduced this issue, unfortunately, I am not able to find any solution to overcome this issue. So I suggest that you could submit any feedback to Access UserVoice:

    https://access.uservoice.com/

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, September 2, 2016 8:22 AM
    • Marked as answer by David_JunFeng Monday, September 5, 2016 5:26 AM
    Friday, August 26, 2016 5:20 AM

All replies

  • In Access 2016, I get the same result (Expr1 is True, Expr2 is False) as you do in Access 2013. I don't think that anything is different in the 64bit versions of Access.

    But this is an interesting question; unfortunately I don't know a solution or workaround.

    Matthias Kläy, Kläy Computing AG

    Wednesday, August 24, 2016 4:22 PM
  • I don't use the 64-bit version, but we can surmise that the literal numbers you are using are going to be 64-bit integers. You are curiously using negative values (perhaps assuming integer wrapping will happen in your favor), and I think it's VERY likely that those would end up being different in the two bitness versions. I would definitely use positive values, and perhaps additionally make sure the constants have the right data type:

    const CharA722 as Long = 123456   'or whatever the positive number would be


    -Tom. Microsoft Access MVP

    Wednesday, August 24, 2016 5:27 PM
  • My bad, the decimal numbers in the example were incorrect. I corrected them, the outcome is unaffected.
    Wednesday, August 24, 2016 5:32 PM
  • Maybe you can call a VBA function from your query, because apparently VBA knows the difference:

    (in the Immediate window)
    ?(ChrW(42786) = ChrW(42841))
    False


    -Tom. Microsoft Access MVP

    Wednesday, August 24, 2016 5:55 PM
  • Yes, theoretically I can call a VBA function for comparing strings from all my queries and filters. This would solve the problem, but I reckon it should adversely affect the speed of these queries. Besides, some native Access tools like "Filter by selection" will not work, and I have to manually modify all these filters by selection replacing string comparison operators with custom VBA functions.

    Thus,

    SELECT Not StrComp(ChrW(42786),ChrW(42841),1) AS Expr1, Not StrComp("a","b",1) AS Expr2;

    yields the correct result with vbTextCompare (1) as the argument of StrComp.

    However,

    SELECT Not StrComp(ChrW(42786),ChrW(42841),2) AS Expr1, Not StrComp("a","b",2) AS Expr2;

    with vbDatabaseCompare (2) results in the same false result as the examples above.

    Wednesday, August 24, 2016 6:14 PM
  • However, when I create a Function :

    Option Compare Database
    Function a() 
    a = (ChrW(42786) = ChrW(42841))
    End Function

    it correctly returns "False" despite the Option Compare Database. Hence, Option Compare Database works differently than vbDatabaseCompare argument of StrComp function.

    Wednesday, August 24, 2016 6:26 PM
  • Hi Alex F Ilin,

    According to your description, I have made a sample and reproduced this issue. But if you query ChrW(42786), you will get result like below:

    ChrW(42841):

    VBA/VB6 stores its strings internally in what Microsoft documentation used to call "Unicode" but should more accurately be called UTF-16. This means that each character is stored in two bytes (well, actually, some obscure characters can use more).

    And since Microsoft Jet, version 4.0 is it capability to provide Unicode support.

    For more information, please refer to:

    http://www.di-mgt.com.au/howto-convert-vba-unicode-to-utf8.html

    https://support.microsoft.com/en-us/kb/275561

    I think that this issue is more related to VBA, so I suggest that you could post your question on MSDN forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    Thanks for your understanding.

    Thursday, August 25, 2016 7:16 AM
  • I do not think that this problem relates to VBA, because I get the same problem in queries even without any VBA code. When I just compare these two signs (or any two signs from Latin Extended-D) in an Access Query or a Filter, Access acts as if these two signs are identical. But they are two different signs.

    SELECT ""="" AS Expr1;

    This SQL falsely results in a True value without any VBA involved. As noted in the above discussion this problem does not occur in VBA code, but only occurs in Access queries and filters. Hence, the problem concerns the database engine, not the VBA.


    These are exactly the signs I need in my work, but Access cannot properly compare them (falsely returning true for the expression ""="" in queries).
    • Edited by Alex F Ilin Thursday, August 25, 2016 7:57 AM
    Thursday, August 25, 2016 7:46 AM
  • >>>These are exactly the signs I need in my work, but Access cannot properly compare them (falsely returning true for the expression "Ꜣ"="ꝙ" in queries).

    According to your description, I have reproduced this issue, unfortunately, I am not able to find any solution to overcome this issue. So I suggest that you could submit any feedback to Access UserVoice:

    https://access.uservoice.com/

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Friday, September 2, 2016 8:22 AM
    • Marked as answer by David_JunFeng Monday, September 5, 2016 5:26 AM
    Friday, August 26, 2016 5:20 AM

  • Thank you! I created the ticket "Fix the comparison of strings with Unicode Latin Extended-D characters" on Access UniserVoice. I truly hope that developers concerned with the handling of Unicode in MSA will upvote that thread, and Microsoft will eventually fix it.
    Friday, August 26, 2016 8:04 AM