By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Unknown error in validation of a field

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
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


What methods do you use to ensure the textboxes txtAdv0, etc have an
acceptable value? Perhaps somebody could write "text" in the box - or what
would happen if the value was null? Can you ensure it is a whole number -
or whatever you actually need? If you are expecting whole numbers then why
not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you will convert all
textbox values to a legitimate value, a zero if left blank or generate an
error if the textbox value can't be converted to a whole number.

Nov 13 '05 #2

P: n/a
Thanks Eric. As far as I know the data is all numbers, they have three
decimal places so the field property is set Double and the control property
is set to General Number with three decimal places. I've actually keyed some
of the data myself to check and then manually checked the addition. The
addition is correct but the message box pops up to say the addition is
incorrect but gives the sum as being the figure that has been input so they
match. The really weird thing is it happens on a small number and there
doesn't seem to be a pattern.
Thanks for your input
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@sparta.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
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
What methods do you use to ensure the textboxes txtAdv0, etc have an
acceptable value? Perhaps somebody could write "text" in the box - or

what would happen if the value was null? Can you ensure it is a whole number -
or whatever you actually need? If you are expecting whole numbers then why not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you will convert all
textbox values to a legitimate value, a zero if left blank or generate an
error if the textbox value can't be converted to a whole number.


Nov 13 '05 #3

P: n/a
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@titan.btinternet.com...
Thanks Eric. As far as I know the data is all numbers, they have three
decimal places so the field property is set Double and the control
property
is set to General Number with three decimal places. I've actually keyed
some
of the data myself to check and then manually checked the addition. The
addition is correct but the message box pops up to say the addition is
incorrect but gives the sum as being the figure that has been input so
they
match. The really weird thing is it happens on a small number and there
doesn't seem to be a pattern.
Thanks for your input
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@sparta.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
>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


What methods do you use to ensure the textboxes txtAdv0, etc have an
acceptable value? Perhaps somebody could write "text" in the box - or

what
would happen if the value was null? Can you ensure it is a whole
number -
or whatever you actually need? If you are expecting whole numbers then

why
not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you will convert
all
textbox values to a legitimate value, a zero if left blank or generate an
error if the textbox value can't be converted to a whole number.

I would guess this is a rounding error. Paste the code below into a new
module, press CTRL+G to get the imediate window and type

Call TestIt

You may be horrified to find that at the end of the routine, the number does
not add up to exactly 1. You may need to call some rounding function, but
certainly, at least to start with, your code should identify the size of the
discrepancy. So your code would say "discrepancy is 0.00001" You then know
it is a rounding error which you can accommodate by coding.

Public Sub TestIt()

Dim lngCount As Long
Dim dblNumber As Double

For lngCount = 1 To 1000000
dblNumber = dblNumber + 0.000001
Next lngCount

MsgBox dblNumber

End Sub

Nov 13 '05 #4

P: n/a
Thanks Eric ran the module. Got an answer 1.00000000000792
This is beyond my level of expertise, but can you point me in the right
direction. I'm a newbie when it comes to VBA
Thanks
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@titan.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@titan.btinternet.com...
Thanks Eric. As far as I know the data is all numbers, they have three
decimal places so the field property is set Double and the control
property
is set to General Number with three decimal places. I've actually keyed
some
of the data myself to check and then manually checked the addition. The
addition is correct but the message box pops up to say the addition is
incorrect but gives the sum as being the figure that has been input so
they
match. The really weird thing is it happens on a small number and there
doesn't seem to be a pattern.
Thanks for your input
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@sparta.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
>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

What methods do you use to ensure the textboxes txtAdv0, etc have an
acceptable value? Perhaps somebody could write "text" in the box - or

what
would happen if the value was null? Can you ensure it is a whole
number -
or whatever you actually need? If you are expecting whole numbers then

why
not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you will convert
all
textbox values to a legitimate value, a zero if left blank or generate

an error if the textbox value can't be converted to a whole number.

I would guess this is a rounding error. Paste the code below into a new
module, press CTRL+G to get the imediate window and type

Call TestIt

You may be horrified to find that at the end of the routine, the number

does not add up to exactly 1. You may need to call some rounding function, but
certainly, at least to start with, your code should identify the size of the discrepancy. So your code would say "discrepancy is 0.00001" You then know it is a rounding error which you can accommodate by coding.

Public Sub TestIt()

Dim lngCount As Long
Dim dblNumber As Double

For lngCount = 1 To 1000000
dblNumber = dblNumber + 0.000001
Next lngCount

MsgBox dblNumber

End Sub


Nov 13 '05 #5

P: n/a
Eric I've just found an article in the Microsoft Knowledge base which covers
floating point errors. It suggests using CDec before each calculation or
splitting the calculation into two parts. Can you give me some guidance
here?
Thanks
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@titan.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@titan.btinternet.com...
Thanks Eric. As far as I know the data is all numbers, they have three
decimal places so the field property is set Double and the control
property
is set to General Number with three decimal places. I've actually keyed
some
of the data myself to check and then manually checked the addition. The
addition is correct but the message box pops up to say the addition is
incorrect but gives the sum as being the figure that has been input so
they
match. The really weird thing is it happens on a small number and there
doesn't seem to be a pattern.
Thanks for your input
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@sparta.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
>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

What methods do you use to ensure the textboxes txtAdv0, etc have an
acceptable value? Perhaps somebody could write "text" in the box - or

what
would happen if the value was null? Can you ensure it is a whole
number -
or whatever you actually need? If you are expecting whole numbers then

why
not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you will convert
all
textbox values to a legitimate value, a zero if left blank or generate

an error if the textbox value can't be converted to a whole number.

I would guess this is a rounding error. Paste the code below into a new
module, press CTRL+G to get the imediate window and type

Call TestIt

You may be horrified to find that at the end of the routine, the number

does not add up to exactly 1. You may need to call some rounding function, but
certainly, at least to start with, your code should identify the size of the discrepancy. So your code would say "discrepancy is 0.00001" You then know it is a rounding error which you can accommodate by coding.

Public Sub TestIt()

Dim lngCount As Long
Dim dblNumber As Double

For lngCount = 1 To 1000000
dblNumber = dblNumber + 0.000001
Next lngCount

MsgBox dblNumber

End Sub


Nov 13 '05 #6

P: n/a
Eric having read all the help on data types I changed the data type from
Double to decimal and set the format to Fixed with 3 decimal places and the
problem seems to have gone away. Thanks for your guidance on this, if you
hadn't pointed me in the direction of the floating point problem I wouldn't
have known where to look.
Thanks again
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@titan.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@titan.btinternet.com...
Thanks Eric. As far as I know the data is all numbers, they have three
decimal places so the field property is set Double and the control
property
is set to General Number with three decimal places. I've actually keyed
some
of the data myself to check and then manually checked the addition. The
addition is correct but the message box pops up to say the addition is
incorrect but gives the sum as being the figure that has been input so
they
match. The really weird thing is it happens on a small number and there
doesn't seem to be a pattern.
Thanks for your input
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@sparta.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
>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

What methods do you use to ensure the textboxes txtAdv0, etc have an
acceptable value? Perhaps somebody could write "text" in the box - or

what
would happen if the value was null? Can you ensure it is a whole
number -
or whatever you actually need? If you are expecting whole numbers then

why
not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you will convert
all
textbox values to a legitimate value, a zero if left blank or generate

an error if the textbox value can't be converted to a whole number.

I would guess this is a rounding error. Paste the code below into a new
module, press CTRL+G to get the imediate window and type

Call TestIt

You may be horrified to find that at the end of the routine, the number

does not add up to exactly 1. You may need to call some rounding function, but
certainly, at least to start with, your code should identify the size of the discrepancy. So your code would say "discrepancy is 0.00001" You then know it is a rounding error which you can accommodate by coding.

Public Sub TestIt()

Dim lngCount As Long
Dim dblNumber As Double

For lngCount = 1 To 1000000
dblNumber = dblNumber + 0.000001
Next lngCount

MsgBox dblNumber

End Sub


Nov 13 '05 #7

P: n/a

"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@sparta.btinternet.com...
Eric having read all the help on data types I changed the data type from
Double to decimal and set the format to Fixed with 3 decimal places and
the
problem seems to have gone away. Thanks for your guidance on this, if you
hadn't pointed me in the direction of the floating point problem I
wouldn't
have known where to look.
Thanks again
Tony

Glad you got it sorted. It is definitely something you need to bear in mind
when you are dealing with numbers other than whole ones. Imagine writing a
function which took a number (held as a double) and kept adding 0.000001 to
it, but stopped when the total = 1. You might expect the code to run a
million times, but due to rounding errors it would never be exactly 1 and
the code would run forever.
You may also need this function at some point:
http://www.mvps.org/access/modules/mdl0054.htm

"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@titan.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@titan.btinternet.com...
> Thanks Eric. As far as I know the data is all numbers, they have three
> decimal places so the field property is set Double and the control
> property
> is set to General Number with three decimal places. I've actually keyed
> some
> of the data myself to check and then manually checked the addition. The
> addition is correct but the message box pops up to say the addition is
> incorrect but gives the sum as being the figure that has been input so
> they
> match. The really weird thing is it happens on a small number and there
> doesn't seem to be a pattern.
> Thanks for your input
> Tony
> "Eric Schittlipz" <er**@schittlipz.com> wrote in message
> news:cs**********@sparta.btinternet.com...
>> "Tony Williams" <tw@tcpinvalid.com> wrote in message
>> news:cs**********@hercules.btinternet.com...
>> >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
>>
>> What methods do you use to ensure the textboxes txtAdv0, etc have an
>> acceptable value? Perhaps somebody could write "text" in the box - or
> what
>> would happen if the value was null? Can you ensure it is a whole
>> number -
>> or whatever you actually need? If you are expecting whole numbers
>> then
> why
>> not have something like this:
>>
>> If Not IsNull(txtAdv0) Then
>> lngTotal = lngTotal + CLng(txtAdv0)
>> End If
>>
>> carry on till you'veadded up all textboxes. That way you will convert
>> all
>> textbox values to a legitimate value, a zero if left blank or generate an >> error if the textbox value can't be converted to a whole number.

I would guess this is a rounding error. Paste the code below into a new
module, press CTRL+G to get the imediate window and type

Call TestIt

You may be horrified to find that at the end of the routine, the number

does
not add up to exactly 1. You may need to call some rounding function,
but
certainly, at least to start with, your code should identify the size of

the
discrepancy. So your code would say "discrepancy is 0.00001" You then

know
it is a rounding error which you can accommodate by coding.

Public Sub TestIt()

Dim lngCount As Long
Dim dblNumber As Double

For lngCount = 1 To 1000000
dblNumber = dblNumber + 0.000001
Next lngCount

MsgBox dblNumber

End Sub



Nov 13 '05 #8

P: n/a
"Tony Williams" <tw@tcpinvalid.com> wrote in
news:cs**********@titan.btinternet.com:
Thanks Eric. As far as I know the data is all numbers, they
have three decimal places so the field property is set Double
and the control property is set to General Number with three
decimal places. I've actually keyed some of the data myself to
check and then manually checked the addition. The addition is
correct but the message box pops up to say the addition is
incorrect but gives the sum as being the figure that has been
input so they match. The really weird thing is it happens on a
small number and there doesn't seem to be a pattern.
Thanks for your input
Tony
"Eric Schittlipz" <er**@schittlipz.com> wrote in message
news:cs**********@sparta.btinternet.com...
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:cs**********@hercules.btinternet.com...
>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


What methods do you use to ensure the textboxes txtAdv0, etc
have an acceptable value? Perhaps somebody could write
"text" in the box - or

what
would happen if the value was null? Can you ensure it is a
whole number - or whatever you actually need? If you are
expecting whole numbers then

why
not have something like this:

If Not IsNull(txtAdv0) Then
lngTotal = lngTotal + CLng(txtAdv0)
End If

carry on till you'veadded up all textboxes. That way you
will convert all textbox values to a legitimate value, a zero
if left blank or generate an error if the textbox value can't
be converted to a whole number.

The problem may be that the double datatype is introducing some
very small rounding errors.

I'd rework your code as follows, first to reduce the redundaqnt
calculations, and second to make the comparison allow for a small
rounding error.

Private Sub txtAdvTot_BeforeUpdate(Cancel As Integer)
Dim result as double

result = ([txtAdv0] + [txtAdv500] + [txtAdv1000] + [txtAdv5000] +
[txtAdv10000] + [txtAdv50000] + [txtAdv100000])

If abs(result - [txtAdvTot]) <.001 Then
If MsgBox(" Column 1 does not add up" & vbCrLf & "It should be "
& result & " - Do you want to accept the error?", vbYesNo,
"Calculation Error") = vbNo Then
Cancel = True
End If
End If
End Sub
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.