CODEWITHSUNDEEP
vbams-access
Carlo
Carlo

Reputation: 33

Data type Number-Decimal

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

Upvotes: 3

Views: 742

Answers (1)

Gustav
Gustav

Reputation: 55816

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.

Upvotes: 3

Related Questions