四舍五入 [英] Rounding in Access

问题描述

以下代码可以正常工作，但是并不能将存储的值四舍五入到最接近的便士，例如，出现8.025而不是8.01的人可以提出解决方案吗?

The following code is working fine, however it is not rounding the stored values to the nearest pence, for example 8.025 is coming up instead of 8.01 can anyone suggest a fix?

Public Function Fs_Update_AccInvoices_Nexum() As Boolean
    Dim adoRsInvoiceDCID As New ADODB.Recordset
    Dim adoRsNexumInvoices As New ADODB.Recordset

    On Error Resume Next
    adoRsInvoiceDCID.Open "SELECT * FROM [tInvoiceDCID] where Issued=0" _
        , CurrentProject.Connection, 2, 2
    While Not adoRsInvoiceDCID.EOF
        adoRsNexumInvoices.Open "SELECT * FROM [tPrintInvoiceNumbersNexum] " _
            & " WHERE InvoiceID=" & adoRsInvoiceDCID("InvoiceID") _
            , CurrentProject.Connection, 2, 2
        If Not adoRsNexumInvoices.EOF Then
            DoCmd.SetWarnings off
            DoCmd.RunSQL "Update [Acc Invoices t Nexum] " _
                & " SET [Total Due] = Round((Fees/0.8)+(VAT/0.8)+OutLays,2)" _
                & " Fees = Round(Fees/0.8,2), VAT = Round(Vat/0.8,2)" _
                & " WHERE Invoice=" & adoRsNexumInvoices("PrintingasINVOICE")
        End If
        adoRsNexumInvoices.Close

        adoRsInvoiceDCID.MoveNext
    Wend
    adoRsInvoiceDCID.Close
End Function

欢呼 罗斯

推荐答案

快速说明: 我注意到vba的舍入功能存在一些错误，格式功能无法解决.在我的特定情况下，我试图将数字3687.23486取整

Quick note: I've noticed some inaccuracies in vba's rounding function which the format function doesn't fix. In my particular case, I was trying to round the number 3687.23486

回合(3687.23486)= 3687.23

round(3687.23486) = 3687.23

format(3687.23486，#.00")= 3687.23

format(3687.23486, "#.00") = 3687.23

在传统的四舍五入规则下，结果应为3687.24 我已经看到一些自定义函数已发布到各个论坛上，以解决舍入问题，但是没有一个对我有用，因此我编写了自己的函数.

under the traditional round to nearest rules, this should result in 3687.24 I've seen several custom functions posted to various forums to address rounding problems, but none worked for me, so I wrote my own.

    Function trueRound(ByVal varNumber As Variant, ByVal intDecimals As Integer) As Double
    If IsNull(varNumber) Then
        trueRound = 0
        Exit Function
    End If
    Dim decimals As Integer, testNumber As Double
    decimals = 0
    If InStr(varNumber, ".") > 0 Then decimals = Int(Len(varNumber)) - Int(Len(Fix(varNumber)) + 1)
    If decimals = 0 Or intDecimals > decimals Then
        trueRound = varNumber
        Exit Function
    End If
    Do Until Len(varNumber) - Len(Fix(varNumber)) - 1 <= intDecimals
        testNumber = varNumber * 10 ^ (decimals - 1)
        varNumber = Round(testNumber, 0) / 10 ^ (decimals - 1)
        decimals = decimals - 1
    Loop
    trueRound = varNumber
End Function

我很快就对其进行了哈希处理，因此没有错误处理，并且传递给该函数的null值导致0，这对于所有情况而言可能都不理想.我会在一些非常大的查询中定期使用它，希望它可以对其他人有所帮助.

I hashed it out pretty quick, so there's no error handling, and a null value passed to the function results in 0, which may not be ideal for all situations. I use this regularly in some pretty large queries, hope it can help someone else.

这篇关于四舍五入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！