# Thread: calc on form doesn't compute

1. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20

## Unanswered: calc on form doesn't compute

I'm setting up a test field on a form, in Access 97, that lets the user know if two of the numbers add up to the third number.

A + B = C, okay?

I have a textbox that turns red if A + B <> C. If A + B = C the textbox stays gray. That's the concept.

The problem is that some of the records I know add up correctly, but the textbox turns red anyway.

Ex. 0.65 + 0.06 = 0.71 ==> grey
but 0.65 + 0.05 = 0.70 ==> red <==== This ain't right!!

Can anyone help me to determine what the problem is?

My code for the textbox is as follows:

Private Sub Text4_Enter()
If [Field1] + Field2 <> Text2.Value Then
Text4.BackColor = 255
Else
Text4.BackColor = -2147483633
End If
End Sub

Thanks so much.

Richard

2. Registered User
Join Date
Nov 2003
Location
LONDON
Posts
238

## Re: calc on form doesn't compute

Originally posted by RichardTX
I'm setting up a test field on a form, in Access 97, that lets the user know if two of the numbers add up to the third number.

A + B = C, okay?

I have a textbox that turns red if A + B <> C. If A + B = C the textbox stays gray. That's the concept.

The problem is that some of the records I know add up correctly, but the textbox turns red anyway.

Ex. 0.65 + 0.06 = 0.71 ==> grey
but 0.65 + 0.05 = 0.70 ==> red <==== This ain't right!!

Can anyone help me to determine what the problem is?

My code for the textbox is as follows:

Private Sub Text4_Enter()
If [Field1] + Field2 <> Text2.Value Then
Text4.BackColor = 255
Else
Text4.BackColor = -2147483633
End If
End Sub

Thanks so much.

Richard
Are you looking at the form as a datasheet or in continous mode? If you are then the column of the text box will be coloured for whatever record is the current record.

If you are looking at the form in single form mode then use the debug window and step through your code and see what values it is calculating. As it stands the code looks ok - the only issue I can see is that you should use the Afterupdate event rather then the Enter event as it is higher up the chain of events and is designed for reacting to changes made to a controls value. The enter event is triggered when you enter a new control

3. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
Justin_tighe,

I'm using single form. I'll try the afterupdate event and see what happens. I've tried this also on the oncurrent event with the same results. I'll let you know how it comes out.

Richard

4. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
Exactly the same results.

Sorry. It still no worky.

5. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
It's like it evaulates

0.65 + 0.05 = 0.7 as false and
0.65 + 0.05 <> 0.7 as true

I've tried it both ways. When I was going to school, .65 and .05 was .70. I stepped through the debugger and it returned the right values for each object but it evaluated 0.65 + 0.05 as not being equal to 0.7.

Somebody's not right!

Can someone duplicate this?

Richard

6. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Sounds like you're getting a promotion to float or double and there is the inherent rounding error involved ... Something you could do: multiply each field by 100 and make the comparison using integer math ...

7. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
Multiplying by 100 relieved the problem for some of the records but made it worse for others. I've also tried multiplying by 1000 and 10,000. Not much help. It just moves the problem.

How would I get around this?

8. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
How do I make sure it's a floating point problem? I really don't think it is.

9. Registered User
Join Date
Oct 2003
Location
Roanoke, Va
Posts
445
Originally posted by RichardTX
How do I make sure it's a floating point problem? I really don't think it is.
How are the numbers in the text boxes formatted? It sounds like data bound to a table. Is that the case?

Just for grins, I used two unbound text boxes formatted for General Number and used a message box to compute them and I got the correct results.

Hope it works out.

Gregg

10. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## the tricky part is .....

At first you say

text1 + text2 = 'ok no prob
but if you then compare it with this

text3.VALUE 'it will allways do waht you dont expect

do the following

if val(text1.text) + val(text1.text) = val(text2.text) then

jump in the air and shout
else
hit the user
end if

11. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
Okay. These textboxes ARE bound to a table. The table fields are set as doubles.

I'm doing this for error checking. I want the data entry person to enter A, B and C. A + B has to equal C. If not I want an unbound text box to turn red. If so, I want the unbound textbox to remain grey and hidden.

My code is as follows:

Private Sub txtField3_AfterUpdate()
' make the variables
Dim varField1 As Variant
Dim varField2 As Variant
Dim varField3 As Variant

' populate the variables with data from form
Forms.Form1!txtField1.SetFocus
varField1 = Val(Forms.Form1!txtField1.Text)
Forms.Form1!txtField2.SetFocus
varField2 = Val(Forms.Form1!txtField2.Text)
Forms.Form1!txtField3.SetFocus
varField3 = Val(Forms.Form1!txtField3.Text)

' test variables and display results of test with color
If varField1 + varField2 <> varField3 Then
Me!Text4.SetFocus
Text4.BackColor = 255
Else
Me!Text4.SetFocus
Text4.BackColor = -2147483633
End If
End Sub
Last edited by RichardTX; 11-18-03 at 14:55.

12. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
with the above coding, it still doesn't work when you use .65 + .05 = .7!

I appreciate the help. I'm obviously in a learning curve, here.

13. Registered User
Join Date
Sep 2003
Location
Texas
Posts
20
I appreciate so much you all helping me. It was a combination of everyone's help that lead to this solution for me.

So you'll know, I'm posting the code here.

Private Sub txtField3_AfterUpdate()
Dim dblField1 As Double
Dim dblField2 As Double
Dim dblField3 As Double

Forms.Form1!txtField1.SetFocus
dblField1 = Val(Forms.Form1!txtField1.Text) * 100
Forms.Form1!txtField2.SetFocus
dblField2 = Val(Forms.Form1!txtField2.Text) * 100
Forms.Form1!txtField3.SetFocus
dblField3 = Val(Forms.Form1!txtField3.Text) * 100

If Val(dblField1 + dblField2) <> Val(dblField3) Then
Me!Text4.SetFocus
Text4.BackColor = 255
Else
Me!Text4.SetFocus
Text4.BackColor = -2147483633
End If
End Sub

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•