Error in Decimal variable to text datatype comparison

Question

While comparing variable with data from ADODB.Recordset gives results as tabulated below:

• AssYear string variable = “2022-2023”

• rs1!FromAY text column in mssaccess = “2020”

• typename(rs1!FromAY) - Field

   Double(variable in vb6) and text(datatype in mdb)
   val(Mid(AssYear, 1, 4)) >= rs1!FromAY                   TRUE
  
   Decimal(variable in vb6) and text(datatype in mdb)
   cdec(Mid(AssYear, 1, 4)) >= rs1!FromAY                  FALSE
  

While comparing Decimal variable and text(datatype in mdb) via ADODB.Recordset it returns false(incorrect result) but for Double variable and text(datatype in mdb) comparison it returns true.

I can't use Double due to roundoff error. How to compare Decimal variable to text in ADODB.Recordset ?

The below methods works. what is the reason for the above error?

cdec("2022") >= cdec(rs1!FromAY)                        TRUE
cdec("2022") >= cstr(rs1!FromAY)                        TRUE

Answer 1

This works:

CDec(Mid("2022-2023", 1, 4)) >= "2020"    

so try with:

CDec(Mid(AssYear, 1, 4)) >= rs1!FromAY.Value

or:

CDec(Mid(AssYear, 1, 4)) >= Val(rs1!FromAY.Value)

But no reason to use CDec here. So, as you wish to compare integer numbers, why not:

Val(Mid(AssYear, 1, 4)) >= Val(rs1!FromAY.Value)