That error that only happens sometimes! calculation check on a form

[Tony Williams]

I have a form where the user inputs a series of figures into a number of
controls. After they've input the total (they don't want the program to
create the total) there is a check to make sure the total of the controls
equals the totals they've input. Here is the code in the Before Update
property:

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] +
[txtAdv50000] + [txtAdv100000]) <> [txtAdvTot] Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be " & [txtAdv0]
+ [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] + [txtAdv50000]
+ [txtAdv100000] & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

However in certain cases, and there is no pattern I can assure you, the
message box pops up and says that the total is wrong and repeats the total
that the user has input, in other words it isn't wrong. We've manually added
up all the controls to check that the total is correct and it is. This is
happening in about 3 out 15 cases. What can be causing this it's driving us
MAD!
TIA
Tony Williams

 

[Douglas J. Steele]

What's the data type of the fields? If they're Integer or Long Integer, you
shouldn't have that problem (unless the total is larger than the data type
can handle, but you'd get an overflow error in that case). If they're Single
or even Double, you're probably experiencing floating point round off error.
The usual solution is not to compare the values, but to see whether the
difference between the values is less than a predefined limit.

 

[Eric Schittlipz]

I have a form where the user inputs a series of figures into a number of
controls. After they've input the total (they don't want the program to
create the total) there is a check to make sure the total of the controls
equals the totals they've input. Here is the code in the Before Update
property:

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000]
+
[txtAdv50000] + [txtAdv100000]) <> [txtAdvTot] Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be " &
[txtAdv0]
+ [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] +
[txtAdv50000]
+ [txtAdv100000] & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

However in certain cases, and there is no pattern I can assure you, the
message box pops up and says that the total is wrong and repeats the total
that the user has input, in other words it isn't wrong. We've manually
added
up all the controls to check that the total is correct and it is. This is
happening in about 3 out 15 cases. What can be causing this it's driving
us
MAD!
TIA
Tony Williams

Tony
Do you know the difference between cross-posting and multi-posting? You are
asking multiple groups of people to solve your problem (for free) without
them knowing you have asked in other newsgroups. This duplication of other
people's effort is not considered polite.

 

[Tony Williams]

Sorry Eric I apologise for that it is just that sometimes my postings on the
Microsoft groups don't always seem to appear so I occasionally post on the
other group to make sure my question is posted somewhere.
Again my apologies if I have offended anyone
Tony

I have a form where the user inputs a series of figures into a number of
controls. After they've input the total (they don't want the program to
create the total) there is a check to make sure the total of the controls
equals the totals they've input. Here is the code in the Before Update
property:

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000]
+
[txtAdv50000] + [txtAdv100000]) <> [txtAdvTot] Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be " &
[txtAdv0]
+ [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] +
[txtAdv50000]
+ [txtAdv100000] & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

However in certain cases, and there is no pattern I can assure you, the
message box pops up and says that the total is wrong and repeats the total
that the user has input, in other words it isn't wrong. We've manually
added
up all the controls to check that the total is correct and it is. This is
happening in about 3 out 15 cases. What can be causing this it's driving
us
MAD!
TIA
Tony Williams

Tony
Do you know the difference between cross-posting and multi-posting? You are
asking multiple groups of people to solve your problem (for free) without
them knowing you have asked in other newsgroups. This duplication of other
people's effort is not considered polite.

 

[Tony Williams]

Thanks Douglas. The data type of the fields in the table are Double because
the numbers have three decimal places. The controls on the form are General
Numbers with 3 decimal places. I didn't think there would be any rounding up
problems because I am just adding the figures together. I apologise for my
ignorance but I don't quite follow your last sentence. What I want to do is
check that the total the user has input is the total of the numbers they've
input in each of the controls.
Thanks
Tony

What's the data type of the fields? If they're Integer or Long Integer, you
shouldn't have that problem (unless the total is larger than the data type
can handle, but you'd get an overflow error in that case). If they're Single
or even Double, you're probably experiencing floating point round off error.
The usual solution is not to compare the values, but to see whether the
difference between the values is less than a predefined limit.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form where the user inputs a series of figures into a number of
controls. After they've input the total (they don't want the program to
create the total) there is a check to make sure the total of the controls
equals the totals they've input. Here is the code in the Before Update
property:

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000]
+
[txtAdv50000] + [txtAdv100000]) <> [txtAdvTot] Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be " &
[txtAdv0]
+ [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] +
[txtAdv50000]
+ [txtAdv100000] & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

However in certain cases, and there is no pattern I can assure you, the
message box pops up and says that the total is wrong and repeats the total
that the user has input, in other words it isn't wrong. We've manually
added
up all the controls to check that the total is correct and it is. This is
happening in about 3 out 15 cases. What can be causing this it's driving
us
MAD!
TIA
Tony Williams

 

[Douglas J. Steele]

Rather than

If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000]
+ [txtAdv10000] + [txtAdv50000] + [txtAdv100000])
<> [txtAdvTot] Then

try something like

If Abs(([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000]
+ [txtAdv10000] + [txtAdv50000] + [txtAdv100000])
- [txtAdvTot]) < 0.0001 Then

You might have to play with the 0.0001 value until you're completely happy.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas. The data type of the fields in the table are Double
because
the numbers have three decimal places. The controls on the form are
General
Numbers with 3 decimal places. I didn't think there would be any rounding
up
problems because I am just adding the figures together. I apologise for my
ignorance but I don't quite follow your last sentence. What I want to do
is
check that the total the user has input is the total of the numbers
they've
input in each of the controls.
Thanks
Tony

What's the data type of the fields? If they're Integer or Long Integer, you
shouldn't have that problem (unless the total is larger than the data
type
can handle, but you'd get an overflow error in that case). If they're Single
or even Double, you're probably experiencing floating point round off error.
The usual solution is not to compare the values, but to see whether the
difference between the values is less than a predefined limit.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form where the user inputs a series of figures into a number of
controls. After they've input the total (they don't want the program to
create the total) there is a check to make sure the total of the controls
equals the totals they've input. Here is the code in the Before Update
property:

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000]
+
[txtAdv50000] + [txtAdv100000]) <> [txtAdvTot] Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be " &
[txtAdv0]
+ [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] +
[txtAdv50000]
+ [txtAdv100000] & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

However in certain cases, and there is no pattern I can assure you, the
message box pops up and says that the total is wrong and repeats the total
that the user has input, in other words it isn't wrong. We've manually
added
up all the controls to check that the total is correct and it is. This is
happening in about 3 out 15 cases. What can be causing this it's
driving
us
MAD!
TIA
Tony Williams

 

[Eric Schittlipz]

Sorry Eric I apologise for that it is just that sometimes my postings on
the
Microsoft groups don't always seem to appear so I occasionally post on the
other group to make sure my question is posted somewhere.
Again my apologies if I have offended anyone
Tony

I have a form where the user inputs a series of figures into a number of
controls. After they've input the total (they don't want the program to
create the total) there is a check to make sure the total of the controls
equals the totals they've input. Here is the code in the Before Update
property:

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
If ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000]
+
[txtAdv50000] + [txtAdv100000]) <> [txtAdvTot] Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be " &
[txtAdv0]
+ [txtAdv500] + [txtAdv1000] + [txtAdv5000] + [txtAdv10000] +
[txtAdv50000]
+ [txtAdv100000] & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub

However in certain cases, and there is no pattern I can assure you, the
message box pops up and says that the total is wrong and repeats the total
that the user has input, in other words it isn't wrong. We've manually
added
up all the controls to check that the total is correct and it is. This is
happening in about 3 out 15 cases. What can be causing this it's
driving
us
MAD!
TIA
Tony Williams

Tony
Do you know the difference between cross-posting and multi-posting? You are
asking multiple groups of people to solve your problem (for free) without
them knowing you have asked in other newsgroups. This duplication of other
people's effort is not considered polite.

Not offended - I know postings do "go missing" but if you really have to
re-post, you should make it clear that that's what you are doing. Anyway, I
have answered this in "comp.databases.ms-access"