CODEWITHSUNDEEP
sql-servervbams-access
NamedArray
NamedArray

Reputation: 823

How do you determine the precision/scale of decimals when calculating an expression in Access/VBA?

I'm converting a bunch of Access Data Projects into JavaScript web applications, and I'm using the math.js library to maintain BigNumbers and a finite precision.

There's a field in an Access form that accepts an expression (Formula), and populates another field (Factor) with the answer.

The SQL datatype on the backend is decimal(38, 30); precision of 38, and a scale of 30.

The old application has been running for years; however, it doesn't look like any of the calculations have gone past 28th decimal place.

So, Access/VBA is rounding up to the 28th spot, instead of the 30th spot.

Where does Access/VBA define the scale for a decimal in calculations? 

Private Sub Formula_BeforeUpdate(Cancel As Integer)

    If IsNull(Me.Formula) Or Me.Formula = "" Then
        Me.Factor = ""
    Else
        Me.Result.ControlSource = "=" & Me.Formula
        Me.Factor = Me.Result
    End If

End Sub

// decimal(38, 30)  [DB defined datatype, precision & scale]

//  expression (Formula)
//  ((0.505*193/190)*230/260)/1.1032

//  Access/VBA calculation (Factor):
//  0.411334674542662787386271222800

//  Theoretically expected answer (Factor):
//  0.411334674542662787386271222766

Upvotes: 0

Views: 195

Answers (1)

D Stanley
D Stanley

Reputation: 152521

If you are doing the calculation in VBA, then you are using the VB Decimal type, which only supports 28-29 digits of precision.

Upvotes: 1

Related Questions