Calculation Discrepancy

[Danifrog]

I have a module that contains a simple calculation:

R = (X * D) / V

All values are Double.

Using the following values:

X = 95
D = 10000
V = .0225

I get 42,222,222.22, but Access gives me 42,222,220.5444619.

How is Access calculating this and how can I correct it?

[Scott Price]

Using a calculator, Excel spdsheet, and typing the values into an Access query design grid all produce exactly the same results for me: 42,222,222.22

Let's take a look at the whole code you are using for your function. Also, take a very close look at each data type, especially looking for any times they might go through declaration changes, i.e. from Integer to Double, etc.

Regards,
Scott

p.s. when pasting the code into this reply window, select all the code, wrap it in code tags by clicking the # sign above, and manually edit the first tab to look just like this: [code=vb] Thanks!

[Danifrog]

Thanks Scott,

Hopefully it's somewhere in here. Also in the table where the data is coming from X, V and D are Single.

Public Function cfuQBD(X As Double, V As Double, D As Double) As String
    'X is raw count
    'V is volume
    'D is dilution
 
    Dim R As Double   'Formated Calculated Result
    Dim DR As Double  'Lowest Dilution Result
 
    V = nz(V, 0)
    D = nz(D, 1)
 
    If V = 0 Then
        cfuQBD = vbNullString
        Exit Function
    End If
 
 
    DR = Format(vbaRound((D / V), 0), "#,##0")       'Formated Lowest Dilution Result
        If DR = 0 Then
            DR = 1
        End If
 
    R = Format(vbaRound(((X * D) / V), 0), "#,##0") 'Formated Calculated Resul
 
    Select Case X
        Case Is <= 0
            cfuQBD = "<" & DR
        Case Is > 0
            If R <= 0.5 Then
                cfuQBD = "<" & DR
            Else
                cfuQBD = R
            End If
    End Select
 
End Function

[Scott Price]

Thanks Scott,

Hopefully it's somewhere in here. Also in the table where the data is coming from X, V and D are Single.

Public Function cfuQBD(X As Double, V As Double, D As Double) As String
    'X is raw count
    'V is volume
    'D is dilution
 
    Dim R As Double   'Formated Calculated Result
    Dim DR As Double  'Lowest Dilution Result
 
    V = nz(V, 0)
    D = nz(D, 1)
 
    If V = 0 Then
        cfuQBD = vbNullString
        Exit Function
    End If
 
 
    DR = Format(vbaRound((D / V), 0), "#,##0")       'Formated Lowest Dilution Result
        If DR = 0 Then
            DR = 1
        End If
 
    R = Format(vbaRound(((X * D) / V), 0), "#,##0") 'Formated Calculated Resul
 
    Select Case X
        Case Is <= 0
            cfuQBD = "<" & DR
        Case Is > 0
            If R <= 0.5 Then
                cfuQBD = "<" & DR
            Else
                cfuQBD = R
            End If
    End Select
 
End Function

Where is the vbaRound function coming from? It isn't a vba defined function, so must be user defined somewhere. Could you post the code for it as well?

I suspect the discrepancy is caused by this rounding function.

Working on a copy of your database, try changing to simplify these two lines: DR = (D/V) And R = (X * D) / V)

Any time you are running high precision numbers through a rounding function, it will result in loss of accuracy.

Regards,
Scott

[Danifrog]

When I take out the rounding function (see below) R still equals 42222220.5444619 when X = 95, D = 10000 and V = 0.0225

I added Q to just run the numbers instead of the variables and I get 42222222.2222222.

Option Explicit
 
Public Function cfuQBD(X As Double, V As Double, D As Double) As String
    'X is raw count
    'V is volume
    'D is dilution
 
    Dim R As Double   'Formated Calculated Result
    Dim DR As Double  'Lowest Dilution Result
    Dim Q As Double
 
    V = nz(V, 0)
    D = nz(D, 1)
 
    If V = 0 Then
        cfuQBD = vbNullString
        Exit Function
    End If
 
 
    DR = Format(vbaRound((D / V), 0), "#,##0")       'Formated Lowest Dilution Result
        If DR = 0 Then
            DR = 1
        End If
 
    'R = Format(vbaRound((X * D) / V), 0), "#,##0") 'Formated Calculated Result
    R = X * D / V
    Q = (CLng(95) * CLng(10000)) / 0.0225
 
 
 
    Select Case X
        Case Is <= 0
            cfuQBD = "<" & DR
        Case Is > 0
            If R <= 0.5 Then
                cfuQBD = "<" & DR
            Else
                cfuQBD = R
            End If
    End Select

This is my vbaRound:

Option Compare Database
Option Explicit
 
Public Enum rOpt
    rNearest
    rUp
    rDown
End Enum
 
 
 
Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
    Optional RoundingOption As rOpt = rNearest) As Double
    Dim dblPlacesFactor As Double
    Dim dlbRoundFactor As Double
 
    If intDecimals < 0 Then
        vbaRound = 0
        Exit Function
    End If
 
    dblPlacesFactor = 10 ^ intDecimals
 
    Select Case RoundingOption
    Case rNearest 'Round to Nearest
        dlbRoundFactor = 0.5
    Case rUp 'Round UP
        dlbRoundFactor = 1
    Case rDown 'Round DOWN
        dlbRoundFactor = 0
    End Select
 
    vbaRound = Int(dblValue * dblPlacesFactor + dlbRoundFactor) / dblPlacesFactor
End Function
 
Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
    Optional RoundingOption As rOpt = rNearest) As Double
    Dim dblRoundedMutliple As Double
    Dim dblValueDiv As Double
    Dim dblValueNew As Double
 
     'Set default retrun value if dblRoundTo = 0
    If dblRoundTo = 0 Then
        vbaRoundTO = 0 'OR vbaRoundTO = dblValue
        Exit Function
    End If
 
     'Find multiple of RoundToSmallest
    dblValueDiv = dblValue / dblRoundTo
 
     'Option to RoundUP or RoundDOWN
    Select Case RoundingOption
    Case rNearest
         'Round multiple to nearest
         'DO NOT USE VBA Round() function.
         'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
        dblRoundedMutliple = vbaRound(dblValueDiv, 0)
    Case rUp
         'Round multiple UP
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
    Case rDown
         'Round multiple DOWN
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
    Case Else
    End Select
 
     'Calculate new "rounded-to" value
    dblValueNew = dblRoundedMutliple * dblRoundTo
 
     'Return value
    vbaRoundTO = dblValueNew
 
End Function

[mlcampeau]

It definitely looks like a rounding problem because if you take
10000/.0225=444444.4444444444444444444444444....
Multiply that by 95 and you get your correct answer.
BUT
if you round it to 444444.4
Multiply that by 95, you get 42222218
or if you round it to 444444.44
Multiply that by 95, you get 42222221.8
I don't know enough VBA to help resolve this issue, but, it looks like it's the rounding that's causing it.

[patjones]

It definitely looks like a rounding problem because if you take
10000/.0225=444444.4444444444444444444444444....
Multiply that by 95 and you get your correct answer.
BUT
if you round it to 444444.4
Multiply that by 95, you get 42222218
or if you round it to 444444.44
Multiply that by 95, you get 42222221.8
I don't know enough VBA to help resolve this issue, but, it looks like it's the rounding that's causing it.

This is just a wild guess, but in other languages there is a big difference between 95 and 95.00 say. Even if you declare a variable as a double, I would explicitly put the placeholders in anyway as a matter of standard practice. Don't know if this makes a difference in VBA, but let me know!

[Danifrog]

The function runs twice per record. The first time R = 42222222.2222222 the second time R = 42222220.5444619.

Microsoft says:

"To optimize a query, Microsoft Access will not rerun a custom function unless the value passed to it changes. If the function accepts a field as a parameter, Microsoft Access must rerun the custom function for each record because the data may change from record to record. If criteria are placed on the result of the function, Microsoft Access must rerun the function when applying the criteria."

Any idea what is happening with the numbers the second time?

Debbie

[Scott Price]

When I take out the rounding function (see below) R still equals 42222220.5444619 when X = 95, D = 10000 and V = 0.0225

I added Q to just run the numbers instead of the variables and I get 42222222.2222222.

Option Explicit
 
Public Function cfuQBD(X As Double, V As Double, D As Double) As String
    'X is raw count
    'V is volume
    'D is dilution
 
    Dim R As Double   'Formated Calculated Result
    Dim DR As Double  'Lowest Dilution Result
    Dim Q As Double
 
    V = nz(V, 0)
    D = nz(D, 1)
 
    If V = 0 Then
        cfuQBD = vbNullString
        Exit Function
    End If
 
 
    DR = Format(vbaRound((D / V), 0), "#,##0")       'Formated Lowest Dilution Result
        If DR = 0 Then
            DR = 1
        End If
 
    'R = Format(vbaRound((X * D) / V), 0), "#,##0") 'Formated Calculated Result
    R = X * D / V
    Q = (CLng(95) * CLng(10000)) / 0.0225
 
 
 
    Select Case X
        Case Is <= 0
            cfuQBD = "<" & DR
        Case Is > 0
            If R <= 0.5 Then
                cfuQBD = "<" & DR
            Else
                cfuQBD = R
            End If
    End Select

This is my vbaRound:

Option Compare Database
Option Explicit
 
Public Enum rOpt
    rNearest
    rUp
    rDown
End Enum
 
 
 
Public Function vbaRound(dblValue As Double, intDecimals As Integer, _
    Optional RoundingOption As rOpt = rNearest) As Double
    Dim dblPlacesFactor As Double
    Dim dlbRoundFactor As Double
 
    If intDecimals < 0 Then
        vbaRound = 0
        Exit Function
    End If
 
    dblPlacesFactor = 10 ^ intDecimals
 
    Select Case RoundingOption
    Case rNearest 'Round to Nearest
        dlbRoundFactor = 0.5
    Case rUp 'Round UP
        dlbRoundFactor = 1
    Case rDown 'Round DOWN
        dlbRoundFactor = 0
    End Select
 
    vbaRound = Int(dblValue * dblPlacesFactor + dlbRoundFactor) / dblPlacesFactor
End Function
 
Public Function vbaRoundTO(dblValue As Double, dblRoundTo As Double, _
    Optional RoundingOption As rOpt = rNearest) As Double
    Dim dblRoundedMutliple As Double
    Dim dblValueDiv As Double
    Dim dblValueNew As Double
 
     'Set default retrun value if dblRoundTo = 0
    If dblRoundTo = 0 Then
        vbaRoundTO = 0 'OR vbaRoundTO = dblValue
        Exit Function
    End If
 
     'Find multiple of RoundToSmallest
    dblValueDiv = dblValue / dblRoundTo
 
     'Option to RoundUP or RoundDOWN
    Select Case RoundingOption
    Case rNearest
         'Round multiple to nearest
         'DO NOT USE VBA Round() function.
         'VBA : Round(2.5,0) = 2, i.e. rounds >=0.5 to 0 not 1
        dblRoundedMutliple = vbaRound(dblValueDiv, 0)
    Case rUp
         'Round multiple UP
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 1)
    Case rDown
         'Round multiple DOWN
        dblRoundedMutliple = vbaRound(dblValueDiv, 0, 2)
    Case Else
    End Select
 
     'Calculate new "rounded-to" value
    dblValueNew = dblRoundedMutliple * dblRoundTo
 
     'Return value
    vbaRoundTO = dblValueNew
 
End Function

Just as an interesting exercise I created a table field with type of Single. Plugged in the 0.0225 and then changed the data type to Double. The number then changed to: 0.0225000008940697

Running this number through the calculation results in 42222220.5444619. So what is apparently happening is that your rounding isn't happening soon enough, or isn't happening at all, causing Access/VBA to treat 0.0225 Single data type as 0.0225000008940697 Double data type throughout the calculation. I've got a meeting this afternoon, so won't be able to get back till this evening, but try refining your Rounding function using some Debug.Print's along the way to see where the number is being changed, and how to change (round) it back :-)

Regards,
Scott

[mlcampeau]

Just as an interesting exercise I created a table field with type of Single. Plugged in the 0.0225 and then changed the data type to Double. The number then changed to: 0.0225000008940697

Running this number through the calculation results in 42222220.5444619. So what is apparently happening is that your rounding isn't happening soon enough, or isn't happening at all, causing Access/VBA to treat 0.0225 Single data type as 0.0225000008940697 Double data type throughout the calculation. I've got a meeting this afternoon, so won't be able to get back till this evening, but try refining your Rounding function using some Debug.Print's along the way to see where the number is being changed, and how to change (round) it back

Regards,
Scott


Your post didn't show up Scott so I hit reply and copied and pasted it....weird how it does that. It would only show up on mine after I took away the Quote blocks

[Danifrog]

Yeah! Thank you so much for you help. Yes that seems to be the problem and I can take it from here.

Thanks again,

Debbie

[Scott Price]

Thanks, mlcampeau! I've had it happen before, still not sure why...

Regards,
Scott

[FishVal]

Thanks, mlcampeau! I've had it happen before, still not sure why...

Regards,
Scott

It's likely you've quoted this huge post#5 in your reply. I've noticed this cause a post to be invisible.

Regards,
Fish.