Function Comparisons Class

CompareCoerceDataType

Compare two values to determine if one is less than, equal to, or greater than the other. Attempts to fix-up different types of values to compatible data types.

Signature (Visual Basic / VBA)

Public Function CompareCoerceDataType( _
      ByVal vOne As Variant _
    , ByVal vTwo As Variant _
    , Optional ByVal vCompare As Variant _
    ) As Integer

Code Examples

    CompareCoerceDataType("ALL", "all", vbBinaryCompare) = -1
    CompareCoerceDataType("all", "ALL", vbBinaryCompare) = 1
    CompareCoerceDataType("ALL", "all", vbTextCompare) = 0
    CompareCoerceDataType("3", 3) = 0
    CompareCoerceDataType(Null, 3) = -1
    -1 if vOne < vTwo
     0 if vOne = vTwo
     1 if vOne > vTwo
             vTwo
           E N D S
    vOne E 0 - - -
         N + 0 - -
         D + + n s
         S + + s s
    E = Empty
    N = Null
    D = Date/Numeric
    S = String
    0 = Return 0
    - = Return -1
    + = Return 1
    s = Do String comparison with StrComp
    n = Do numeric/Date comparison

Parameters

Parameter Description
vOne First of two values which are to be compared to determine if it is less than, equal to, or greater than the other.
vTwo Second of two values which are to be compared to determine if it is less than, equal to, or greater than the other.
vCompare Specifies the comparison type and sort order/collating sequence used when comparing string values. vCompare defaults to Binary comparisons if it is missing or Null or cannot be fixed up to a number.

Notes & Version History

Function returns See the following table which describes how comparisons are made depending upon data type of arguments.
Summary Empty is less than Null is less than String/Number. With Number/Date and Number/Date, use numeric comparison. With Number/Date/String and String, use string comparison.