Currency data type is rounding figures...Can you prevent this???

[Mirak]

Hi,

I am comparing two variables of the same currency data type. The first variable (PayinSumAmountCheck) is taken from a single row returned from an sql statement based on a parameter text box which the user inputs. Then second variable2(curTReceiptAmountCheck), however is derived by adding a number of values from an sql statement. The values are stored on the table as currency. The variable2(curTReceiptAmountCheck) which holds the final result is also currency, however, it is rounding the numbers. Thus when the variable1 and varable2 are compared, they do not match because of the rounding. How do I get vb to not round the numbers when performing addition.

Below are the codes I am using.

This is used to get variable1:

Public Function PayinSumAmountCheck() As Currency
'will return the actual amount entered from summary total of paying form
Dim rs As Recordset
Dim strsql As String
strPayinNum = Me.DBCPayinNumber.Text
strsql = ""
strsql = "Select Amount from tblpayinform where Payin_Number = " & "'" & strPayinNum & "'"
Set rs = mdb.OpenRecordset(strsql, dbOpenSnapshot)

Select Case rs.EOF
Case Is = True
PayinSumAmountCheck = 0
rs.Close

Case Is = False
PayinSumAmountCheck = rs!amount
rs.Close
End Select

Set rs = Nothing
End Function

This is used to get the second variable2:

Public Sub CheckTotal()
'This sub will check the total on the receipt against the total addition of
'transactions attached to treasury numbers
On Error GoTo error

Dim strsql As String
On Error Resume Next
strsql = ""
strsql = "SELECT * FROM tblTransactions where TransDate = " & "'" & Me.RtxtDate.Text & "'" & _
" and Branch = " & "'" & strUserBranch & "'" & " and Treasury_Num in (" & _
Me.RtxtTreasuryNum.Text & &quot"

Set rsCheck = mdb.OpenRecordset(strsql, dbOpenSnapshot)
'MsgBox strsql, vbOKOnly

If rsCheck.RecordCount < 0 Then
MsgBox &quot;No records&quot;
Exit Sub
End If

'Loop throug recordset to get grand total
Do Until rsCheck.EOF
curTReceiptAmountCheck = curTReceiptAmountCheck + rsCheck!amount
rsCheck.MoveNext
Loop


Exit Sub
error: MsgBox Err.Description

End Sub

 

[psemianonymous]

Instead of summing everything a bit at a time, use the aggregate function Sum() to get the value instead?

Public Sub CheckTotal()
'This sub will check the total on the receipt against the total addition of
'transactions attached to treasury numbers
On Error GoTo error

Dim strsql As String
On Error Resume Next
strsql = ""

strsql = "SELECT Sum([Amount]) AS TOT FROM tblTransactions where TransDate = " & "'" & Me.RtxtDate.Text & "'" & _
                 " and Branch = " & "'" & strUserBranch & "'" & " and Treasury_Num in (" & _
                  Me.RtxtTreasuryNum.Text & ")"
Set rsCheck = mdb.OpenRecordset(strsql, dbOpenSnapshot)
'MsgBox strsql, vbOKOnly

If rscheck.BOF and rsCheck.EOF and rsCheck.RecordCount = 0 Then

MsgBox "No records"
Exit Sub
End If

'Loop throug recordset to get grand total
 Do Until rsCheck.EOF
      curTReceiptAmountCheck = curTReceiptAmountCheck + rsCheck!TOT
              rsCheck.MoveNext
    Loop


Exit Sub
error: MsgBox Err.Description

End Sub

Of course all this can be replaced by:

Public Sub CheckTotal()
    On Error Resume Next
    Dim whereClause as string

    whereClause = "TransDate = " & "'" & Me.RtxtDate.Value & "'" & _
    " and Branch = " & "'" & strUserBranch & "'" & " and Treasury_Num in (" & _
    Me.RtxtTreasuryNum.Value & ")"

    curTReceiptAmountCheck = curTReceiptAmountCheck + _
Nz(DSum("Amount","tblTransactions",whereClause),0)
End Sub