Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    20

    Thumbs down 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. #2
    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. #3
    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. #4
    Join Date
    Sep 2003
    Location
    Texas
    Posts
    20
    Exactly the same results.

    Sorry. It still no worky.

  5. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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. #10
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Talking 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. #11
    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 15:55.

  12. #12
    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. #13
    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
  •