I have one column array that I'm selecting from a SQL Server, this column is sometimes ascending sorted and sometimes not.
The database I'm selecting from is set to Hebrew_CI_AS collation, and the data in the column contains Hebrew text, English text and wildcards.
In my VBA sub I have this code that should check rather the "ArrayThatReturnsFromSQL" variable is ascending sorted or not.
Sub MySub
Dim ArrayThatReturnsFromSQL as Variant
Dim IsArraySorted as Boolian
'One column array
ArrayThatReturnsFromSQL = ThisFunctionReturnsArrayFromSQL
IsArraySorted = IsArrSortedAsc(ArrayThatReturnsFromSQL)
End Sub
Public Function IsArrSortedAsc(Arr As Variant) as Boolean
Dim aRw As Long
Dim Ub As Long
Dim tmpBool As Boolean
Ub = UBound(Arr)
tmpBool = True
If Ub > 0 Then
For aRw = 1 To Ub
If Arr(aRw) < Arr(aRw - 1) Then
tmpBool = False
Exit For
End If
Next
End If
IsArrSortedAsc = tmpBool
End Function
My IsArrSortedAsc is always returns FALSE even after an "ORDER BY ASC" clause, and I think it's because my Hebrew_CI_AS collation.
I think I can solve it by adding a ORDER BY FieldName COLLATE ????????
, but I don't know what is the VBA "Collation"...
So my question is, what Collation I need to use in the SQL that is similar to the way the VBA ordering is working ?
EDIT:
Ok, I managed to isolate the issue, try the following code, it is clearly a VBA bug.
Sub Test()
Dim STR1 As String
Dim STR2 As String
'Reminder, Hebrew is written from right to left
'so STR1 should be less then STR2
STR1 = "א'"
STR2 = "'א"
'this return 1 it means that string1 is greater than string2, and it's wrong.
Debug.Print StrComp(STR1, STR2, vbTextCompare)
End Sub
Any ideas ?