Currency rounding issues

[odewallrus]

Here is a problem that I am having that seems very simple to me, obviously I am missing something. If some one could help?

I have a house charge field that allows a user to charge all or a portion of a bill. In the AfterUpdate event of the house_charge field is the following code. The problem is that if I enter a house charge amount that is equal to the grand total, in most cases I get the message "A House Charge posted against this work order cannot be greater than the Grand Total, or left blank." I believe that it has to do with the totals being rounded up and thus they are actually less that the house_charge amount. Below I turned on 3 decimal places to show the problem, but in my database I have set currency and 2 decimal places in all forms, tables and queries. Below I am trying to input a value of $340.21 into the house_charge field.

code:
**************************************************

If Me.House_Charge_Amount.Value <= Forms![Customer_information_Form]![Work_Order_Subform]![Work_Order_Customer_Total_Subform]!Grand_Total Then
Me.Other_Payment.SetFocus
Me.House_Charge_Amount.Enabled = False
Me.House_Charge_Amount.Locked = True
Me.Parent.House_Charge_Balance_Subform.Requery

ElseIf MsgBox("A House Charge posted against this work order cannot be greater than the Grand Total, or left blank.", vbOKOnly, "House Charge Help") = vbOK Then
Me.House_Charge_Amount = "0"
Me.House_Charge_Amount_Button.SetFocus
Me.House_Charge_Amount.Enabled = False
Me.House_Charge_Amount.Locked = True

End If


Work_Order_Customer_Total_Subform:
***************************************************
(with 2 decimal places set)
Labor $20.95
Parts $300.00
Sub Total $320.95
Tax $19.26
Grand Total $340.21

(with 3 decimal places set)
Labor $20.95
Parts $300.00
Sub Total $320.95
Tax $19.257
Grand Total $340.207

 

[a.sinatra]

money is always rounded up! there is no rounding down of money.
________
VOLCANO CLASSIC VAPORIZER

 

[odewallrus]

Nowhere in my description did I mention anything about rounding down currency. The problem is when I check the house charge amount against the grand total to make sure that the house charge is less or equal, access does not seem to care that the value displayed in the grand total field is rounded up, the code seems to be looking at the actual value of the grand total and not the rounded up “displayed value”. Sorry if I was unclear in my description.

 

[WayneRyan]

ode,

Regardless of how you display it, the Currency field has the
exact representation stored. Therefore, you can't use it when
you do an "If AmountEntered > GrandTotal ..." or translated
"If 340.21 > 340.207". You are going to need to replace the
340.207 with something like:

(GrandTotal * 1000) + 9)/1000

That should give you 340.21

Maybe someone else can give a clear dissertation, but that
should at least be a temporary work-around.

Wayne

 

[WayneRyan]

ode,

On further review try ...

Forms![Customer_information_Form]![Work_Order_Subform]![Work_Order_Customer_Total_Subform]!Grand_Total + 0.009

Wayne

 

[MarionD]

Hi Ode

Can't you try if amount entered > round([grandtotal],2) then...

You have to have the round Function of course - the ,2 can be replaced bay as many decimal places as you wish to round to


Function Round(dblWert As Variant, intStellen As Integer) As Double
Dim dblDez As Double
Dim dblTemp1 As Double, dblTemp2 As Double

On Error GoTo err_round

dblDez = 10 ^ intStellen
dblTemp1 = Abs(dblWert) * dblDez + 0.5
dblTemp2 = Int("" & dblTemp1) / dblDez
If Abs(dblWert) <> dblWert Then
Round = dblTemp2 * -1
Else
Round = dblTemp2
End If

exit_round:
Exit Function

err_round:
Round = False
Resume exit_round

End Function

 

[Rich]

If your fields are defined as currency then this is wrong
Me.House_Charge_Amount = "0"
"0" is a string

 

[odewallrus]

Thank all of you for the help. Wayne, yours proved to be the simplest solution. Rich thank you for the catch in the “0” I have since corrected it.