四舍五入

问题描述

以下代码可以正常工作，但是并不能将存储的值四舍五入到最接近的便士，例如，出现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.24I'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.

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