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
8  1547 
"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.
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.
"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
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
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
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
"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
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Shabam |
last post by:
I know that dotnet allows for form field validation. However I'm looking to
customize the error message display and am wondering if it's possible to do
what I need.
Example:
Suppose in a...
|
by: Calan |
last post by:
I have a server-side ASP script that dynamically creates an input form from
a database table. The table contains a field name, the table where values
are stored, type of input control, value for a...
|
by: Ryan |
last post by:
A binding navigator control adds the following code for when the Save button is clicked:
Me.Validate()
Me.UserBindingSource.EndEdit()
Me.UserTableAdapter.Update(Me.UserDataSet.User)"
You can...
|
by: ak1dnar |
last post by:
Hi, I got this scripts from this URL
There is Error when i submit the form.
Line: 54
Error: 'document.getElementbyID(....)' is null or not an object
What is this error.
Complete Files
|
by: raj |
last post by:
Hi All,
I have a problem regarding web-service behaviour.
I have used web service behaviour in my application for Database
validation task of Client-codes.
in the same way as it is described in...
|
by: Adrock952 |
last post by:
I have a form which validates fine using classes but i would like the fields that have the error to be highlighted a different color
At the moment I just get a list of errors but would like a...
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I...
|
by: ryan.paquette |
last post by:
Hello'
I have a field in which I require validation in a before update
event.
The validation code I have works fine, no problem there.
However I would like to turn off the default validation...
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
