Really difficult Access question

General Questions
#1

Looking for help. Please try the following.

Create a blank form. Create three text boxes called txtTotal, txtAmount1, txtAmount2. Create a command button. In the click event put the following

Dim amount1 As Double
Dim amount2 As Double
Dim total As Double

amount1 = cdbl(txt1Amount)
amount2 = cdbl(txt2Amount)
total = cdbl(txtTotal)

If amount1 + amount2 <> total Then
Debug.Print x + y
MsgBox “Access cannot add”
End If
Run the form. Put a number into txtAmount1 and txtAmount2. Put the total of these two numbers in txtAmount. Click the button.

Let me know if it displays the message. It shouldnt display the message but for some reason I cant figure out why it does. 25 years of combined programming experience cannot come up with an answer. Any help appreciated.

BTW it does the same thing in Access 97 and 2000.

#2

That first post should have said

amount1 = cdbl(txtAmount1)
amount2 = cdbl(txtAmount2)
total = cdbl(txtTotal)
not

amount1 = cdbl(txt1Amount)
amount2 = cdbl(txt2Amount)
total = cdbl(txtTotal)

#3

I wish I were an Access guru and can help. Unfortunately, I’m just a putzer-arounder in Access.

The best Access help I’ve ever gotten is from the A3 forums (fora?) at www.athree.com . It’s a fantastic resource.

Hope this helps!
Daniel

#4

You don’t say whether or not you’re writing a serious application, but back when I taught programming I encouraged (insisted) that my students start every module (before the first subroutine or function declaration) with Option Explicit. This tells the compiler to generate an error when it encounters undeclared variables. And it has saved me untold hours of debugging.

Disclaimer: I’m not sure whether OE is available in Access, but it is part of VBA for Excel, so I imagine it is in Access as well.

#5

I don’t do a lot with Access (I mostly program in VB), but I put your code into an Access form and got it to work.

The only thing I changed was this:



**Debug print x + y**

I changed that to:



**Debug print Text0 + Text2**
(I used the default object names...so I had Text0, Text2, Text4 and Command6 --->
in your case change x + y to txtAmount1 + txtAmount2)

When I had x + y in there, it was giving me a variable undefined error and wouldn’t even run any of the code until I changed that. I tried entering whole numbers, numbers with small fractions, and numbers with big fractions and I never got that Messagebox to pop up (except when I intentionally entered the Amount value incorrectly).

Floating point variables will sometimes give you rounding errors that could cause a problem, but not Doubles. So the only problem with the code is that x and y were undefined.

#6

No offence but I did say that 25 years of programming experience had tried to figure out what was wrong so yes, this is a test piece of code taken from a serious application. I do not need programming lessons.

#7

You’ve certainly got me intrigued. (FWIW: 19 years programming experience, and I’ve used MS-Access professionally since version 1.1 way back in, oooh, 1993 IIRC)

I don’t think Option Explicit is significant - you’re declaring your variables anyway, 'cept for the x + y already mentioned.

Does it happen for simple integer values?

I tried pasting your code into a form as you suggested and got:

15 + 21 = 36 (no message - as expected)
15 + 21 = 37 (message - as expected)
12.34 + 87.65 = 99.99 (message - unexpected)

The last one surprised me. I suspect it must be a rounding problem to do with holding decimal values in binary form. E.g. sometimes VB/VBA adds 1+1 and gets 2.00000000014 and so on.

#8

I am finding the same thing…

If you ask it to print the numbers it is comparing, then it gives you 99.99 for both amount1 + amount2 as well as total.

Comparing them reveals that Access thinks that amount1 + amount2 > total which would seem to support some kind of rounding error…

Code used:


Dim amount1 As Double
Dim amount2 As Double
Dim total As Double

amount1 = CDbl(txtAmount1)
amount2 = CDbl(txtAmount2)
total = CDbl(txtTotal)

If amount1 + amount2 < total Then
Debug.Print amount1 + amount2
MsgBox "Access cannot add - " & amount1 + amount2 & " < " & total
ElseIf amount1 + amount2 > total Then
Debug.Print amount1 + amount2
MsgBox "Access cannot add - " & amount1 + amount2 & " > " & total
End If

Gp

#9

I agree that it looks suspiciously like a rounding error. What numbers are you testing with?

Is there a particular reason why you are working with doubles?

#10

If you’re using whole numbers and the message box pops up, I’m still going with OttoDaFe’s advice until we have more info.

For those playing along at home :):

  1. Run the program and enter 10 for amount 1, 11 for amount 2, and 21 for total.

  2. When you click the command button, the msgbox should not pop up.

  3. Remove OPTION EXPLICIT from your code.

  4. In the line “amount1 = CDbl(txtAmount1)”, change txtAmount1 (with the numeral one on the end) to txtAmountl (with the lowercase letter L on the end). While it looks different here, they are nearly identical in the VB editor with standard fonts.

  5. Run the program and enter 10 for amount 1, 11 for amount 2, and 21 for total.

  6. When you click on the command button, the msgbox will appear.

The use of OPTION EXPLICIT would cause the code to abort when attempting part 6, thus alerting you to the problem.

If that isn’t the problem, and the message box appears when using whole numbers, I’d run the debugger on it and see what the values of the three variables are.

#11

I’m just testing with small numbers like 1.1 + 2.2 <> 3.3 (I should have mentioned earlier that I was using floating point numbers not integers)

As for using doubles? why not, they are the most precise numberic type so I tend to use them by default.

If i do (amount1 + amount2) - total
I get
4.44089209850063E-16

So for some reason the conversion to double is introducing a change to the number being converted. Strange :frowning:

#12

Since it looks like it only appears with floats, make the following code changes (highlighted in bold) and your problem will be solved:

Dim amount1 As Currency
Dim amount2 As Currency
Dim total As Currency

amount1 = CCur(txtAmount1)
amount2 = CCur(txtAmount2)
total = CCur(txtTotal)

If amount1 + amount2 < total Then
Debug.Print amount1 + amount2
MsgBox "Access cannot add - " & amount1 + amount2 & " < " & total
ElseIf amount1 + amount2 > total Then
Debug.Print amount1 + amount2
MsgBox "Access cannot add - " & amount1 + amount2 & " > " & total
End If

#13

The problem isn’t rounding, the problem is the representation of doubles internally. The very slight difference between amount1+amount2 and total is a quirk of that representation, with the least significant digit being different following addition than following conversion.

The reason switching double to currency works is because currency is a fixed place represenation. Doubles are the most precise type only insofar as significant digits are considered.

#14

Ah! There’s your trouble. Floating point data-types are best suited for scientific types of calculations where the numbers are either extremely large or extremely small. Perhaps that’s what you’re dealing with this specific case, but I don’t think it’s a good idea to use them by default.

#15

For those people saying I should currency instead of double consider this :

I put in 2.2 in txtamount1, 1.1 in txtamount2 and 3.3 in txtTotal. I do the following :

Debug.Print amount1 - 2.2
Debug.Print amount2 - 1.1
Debug.Print total - 3.3
Debug.Print (amount1 + amount2) - 3.3

I get these results

0
0
0
4.44089209850063E-16

So, 2.2 + 1.1 DOES not equal 3.3. Therefore access cannot add or its representation of doubles is plain wrong. If I was using large scientific numbers and it made a rounding error I could understand. But not adding 1.1 + 2.2.

BTW if the run the same piece of code in Visual Basic it works fine. Obviously a double in VB and a double in VBA are not the same thing.

#16

Even though your debug statements would leave you to believe otherwise, your if-then block should still return a false using the examples we gave you. It did, didn’t it?

Assuming it did, the reason that your debug statement gives different results is that it’s not the same equation.

A computer can’t do exact floating point math very well due to storage limitations and differences in built in floating point processors. Currency, on the other hand is very similar to Integer, only divided down to show decimal places. I didn’t write the interpreter, so it may not function exactly this way, but it’s very close to this: 3.3 would be stored internally as 33, and would have a flag indicating that this particular number needs to be divided by 10. 3.33 would be stored as 333 and the flag would indicate to divide it by 100.

Using your example, but with the currency datatypes we talked about, the if-then block is testing this statement for inequality:

CCur(amount1) + CCur(amount2) <> CCur(total)

whereas if we looked at the same line, but using your debug logic, the statement would look like this:

CCur(amount1) + CCur(amount2) <> CSng(total)

Note: I’m not sure which implicit conversion Access makes in this situation, so it could be CDbl(total) at the end of the line. In either case, neither are the same thing as Currency.

To verify this, put CCur around the constants in your debug statements and check the results.

#17

I have to take issue with your statement that “computers cannot do floating point math very well do to storage limitations”. Computers do floating point math very well within the precision limits of how the data is stored. If they didnt then they wouldnt be much use to us.

No other language I’ve programmed in that uses doubles has had a problem doing simple math like this. Currency is an Access specific data type. The fact that it works if you use currency instead of doubles still leaves the question of why it doesnt work with doubles.

Can we agree that it SHOULD work with doubles for the example given and that this is bug(problem, whatever) with Access?

#18

That’s not strange, that’s normal. Floating point numbers are of limited precision. The only way to store numbers with indefinite precision is with either strings or BCD (binary coded decimal).

One of the first things I learned in programming is never compare floating point numbers for equality. If two numbers are within some small tolerance epsilon, they are considered equal. We used to use something like 1/10,000 for single-precision floating numbers.

#19

Computers do floating point math pretty well, but I didn’t argue against that. You may have put quotes around the statement you attributed to me, but it’s certainly not a direct quote.

All languages that handle floating point math have this problem, it just doesn’t always occur under all circumstances.

C++

Fortran

Pascal

The statement (1 / 3) * 3, for example, will often give different results on different computers and different languages.

Feel free to search the net for the term “round-off error”, which is what this is commonly called. There are also a number of algorithms created for the specific purpose of handling this problem. Access has just made your life easier by providing the Currency datatype.

Because of a round-off error. (see above)

Sure, we can agree that it should, but it would require a much more flexible storage arrangement for floats which would slow down floating point math dramatically, and even that wouldn’t handle all numbers. 1/3 is a good example. In math, you’d see something like 0.333…, which we humans can read fine. A computer doesn’t have a clue how to handle that, since it doesn’t have a way to store a number that repeats to infinity.

For the record, the example given works fine on my computer, with my operating system, with my processor, in my version of Access. Thanks to round-off errors, this is not guaranteed to work everywhere, and thus shouldn’t be used for this circumstance.

Finally, while I’m not going to link to the thread in question, I still think you owe someone a very large apology in another thread.

#20

Nope, it’s an inherent problem with floating point numbers; greater accuracy is only available at the expense of greater storage space.

From the MS website:

and