vba - Data type Number-Decimal

Question

until few days ago everything was working fine and then I run into this problem. I wrote some code for Access vba in Microsoft 365 that run SQL query on some local and connected tables. One of this connected table has a field set as data type Number - Decimal. As I mention, few days ago this field start to return empty string. This are few steps I try to investigate the problem.

1. I made a local copy of the connected table to make sure the problem was not coming from outside. No difference
2. I create a simple query access - SELECT * FROM [NameTable] and all the data were there
3. I run the same query in vba and the field in question return an empty string

4. I run the access query within vba

   Set qdfNew = dbs.QueryDefs("Pippo")
   Set RS = qdfNew.OpenRecordset
       If Not (RS.EOF And RS.BOF) Then
           RS.MoveLast
           RS.MoveFirst
           For iCurRec = 0 To RS.RecordCount - 1
               Debug.Print RS.Fields("HSL_QUANTITA").Value
               RS.MoveNext
           Next iCurRec
       End If
   RS.Close
   

It returns an empty string 5. I change the data type of the table into Number - Integer, Long, Single and Double and in all these cases the query in vba return correct value 6. I modify the code in this way

    Set qdfNew = dbs.QueryDefs("Pippo")
    Set RS = qdfNew.OpenRecordset
        If Not (RS.EOF And RS.BOF) Then
            RS.MoveLast
            RS.MoveFirst
            For iCurRec = 0 To RS.RecordCount - 1
                Debug.Print TypeName(RS.Fields("HSL_QUANTITA").Value)
                RS.MoveNext
            Next iCurRec
        End If
    RS.Close

While changing the data type the code return in the immediate windows: String -> Number-Decimal Single -> Number-Single precision Double -> Number-Double precision Integer -> Number-Integer Long -> Number-Long

It looks like since few days ago vba cannot convert the decimal to a String anymore

I do not own the connected table hence I cannot change the data type. I try to report the problem to the the Office help desk but they cannot solve the problem since is vba related.

Any suggestion?

Thanks

Answer 1

You have been hit by a recent bug:

Access VBA/DAO code may crash or report incorrect data for Decimal columns

Notice the included link for a temporary work-around.