VBA Special characters U+2264 and U+2265

Question

I have a frustrating problem. I have a string containg other characters that are not in this list (check link). My string represents a SQL Query.

This is an example of what my string can contain: INSERT INTO test (description) VALUES ('≤ ≥ >= <=')

When I check the database, the row is inserted successfully, but the characters "≤" and "≥" are replaced with "=" character. In the database the string in description column looks like "= = >= <=".

For the most characters I can get a character code. I googled a character code for those two symbols, but I didn't find one. My goal is to check if my string contains this two characters , and afterwards replace them with ">=" and "<="

===Later Edit===

I have tried to check every character in a for loop;

tmp = Mid$(str, i, 1)

tmp will have the value "=" when my for loop reaches the "≤" character, so Excel cannot read this "≤" character in a VB string, then when I'm checking for character code I get the code for "=" (Chr(61))

Your question is unclear. Pls add detail showing your expected input and output and the full code you have tried. — brettdj, Jul 03 '14 at 10:46
input (in Excel cell) : "≤ ≥" and the output (in the SQL Database) : "= =" ; Basically Excel changes the "≤" symbol with "=" — SparX23, Jul 03 '14 at 10:50
The string will be read from a specific cell. A user will write in that cell , then a VBA code will insert that value in the database. — SparX23, Jul 04 '14 at 09:07

score 0 · Accepted Answer · edited May 23 '17 at 11:57

Are you able to figure out what the character codes for both "≤" and "≥" in your database character set are? if so then maybe try replacing both characters in your query string with chrw(character_code).

I have just tested something along the lines of what you are trying to do using Excel as my database - and it looks to work fine.

Edit: assuming you are still stuck and looking for assistance here - could you confirm what database you are working with, and any type information setting for the "description" field you are looking to insert your string into?

Edit2: I am not familiar with SQL server, but isn't your "description" field set up to be of a certain data type? if so what is it and does it support unicode characters? ncharvar, nchar seem to be examples of sql server data types that support Unicode.

It sounds like you may also want to try and add an "N" prefix to the value in your query string - see Do I have use the prefix N in the "insert into" statement for unicode? & how to insert unicode text to SQL Server from query window

Edit3: varchar won't qualify for proper rendering of Unicode - see here What is the difference between varchar and nvarchar?. Can you switch to nvarchar? as mentionned above, you may also want to prefix the values in your query string with 'N' for full effect

Edit4: I can't speak much more about sqlserver, but what you are looking at here is how VBA displays the character, not at how it actually stores it in memory - which is the bottom line. VBA won't display "≤" properly since it doesn't support the Unicode character set. However, it may - and it does - store the binary representation correctly.

For any evidence of this, just try and paste back the character to another cell in Excel from VBA, and you will retrieve the original character - or look at the binary representation in VBA:

Sub test()
    Dim s As String
    Dim B() As Byte

    '8804 is "≤" character in Excel character set
    s = ChrW(8804)

    'Assign memory representation of s to byte array B
    B = s

    'This loop prints "100" and "34", respectively the low and high bytes of s coding in memory
    'representing binary value 0010 0010 0110 0100 ie 8804
    For i = LBound(B) To UBound(B)
        Debug.Print B(i)
    Next i

    'This prints "=" because VBA can not render character code 8804 properly
    Debug.Print s
End Sub

I use Microsoft SQL 2008 R2. "description" is a column of "test" db table. I use VBA, to insert data from excel into the database. I have a lot of rows to insert , so I create a string for every excel row; that string represents a Insert Query. — SparX23, Jul 04 '14 at 10:09
I could change the datatype from varchar to nvarchar, but I don't think the columns datatype is the problem here. If I copy the "≤" character from excel, then paste it in SQL cell, the character will be copied ok. That's why I think that my issue is in Excel. I copy this "≤" character in Excel cell from character map, and if I put a breakpoint in VBA code, this character is transformed in "=". — SparX23, Jul 07 '14 at 06:28
@IAmDranged If you're having trouble logging into your account, see [the help center](http://stackoverflow.com/help/merging-accounts). — Gilles 'SO- stop being evil', Jul 07 '14 at 17:09
I worked this out. I used your example and I check the string before inserting in SQL; So my string will be like "INSERT into test (description) VALUES (" & ChrW(&H2264) & ")" . This way is inserting the right character into database — SparX23, Jul 11 '14 at 12:31

score 0 · Answer 2 · edited May 23 '17 at 11:57

If I copy your text INSERT INTO test (description) VALUES ('≤ ≥ >= <=') and paste it into the VBA editor, it becomes INSERT INTO test (description) VALUES ('= = >= <=').

If I paste that text into a Excel cell or an Access table's text field, it pastes "correctly".

This seems to be a matter of character code supported, and I suggest you have a look at this SO question.

But where in you program does that string come from, since it cannot be typed in VBA ??

Edit: I jus gave it a try with the below code, and it works like a charm for transferring your exotic characters from the worksheet to a table !

Sub test1()

    Dim db As Object, rs As Object, cn As Object
    Set cn = CreateObject("DAO.DBEngine.120")
    Set db = cn.OpenDatabase("P:\Database1.accdb")
    Set rs = db.OpenRecordset("table1")
    With rs
        .addnew
        .Fields(0) = Range("d5").Value
        .Update
    End With
End Sub

VBA Special characters U+2264 and U+2265

2 Answers2