Comparing values odd behaviour?

[hughess7]

Hi all, this is a strange one. I have two fields on a form and after the 2nd field is entered I run code on the beforeupdate event to check if value1 is > value2 and stop the user from entering if so. This has always worked for years and still does most of the time.

BUT on some records it has suddenly started to error stating it is greater than when it is not - they are the same values (=). It allows you to enter any value less than but not equal to. I thought it might be a rounding issue but the data is stored as 2 decimal places and debug.print shows the values as identical in the immediate window. If the user removes Value1 and re-enters it, then enters Value2 again it accepts it. The only change recently is Value1 is being entered from a different database (automated by a routine), but I can't tell if it is just these that are causing the problem and if so why? Any ideas anyone??? Thanks... (Access 2010)

 

[gemma-the-husky]

can you post the code you use please?

out of interest, what sort of values are showing spurious errors. One issue is that because computers cannot represent all "real numbers" - ie with decimal points precisely (eg 0.1 cannot be exactly represented in a computer) - then testing real values sometimes produces spurious errors.

 

[hughess7]

Hi thx, the fields are both numbers, double standard format, and the code is:

If Me![CBFD NSC] > Me![CBFD] Then
Cancel = True
msg = "Value can not be greater than original FD amount. Please re-enter"
MsgBox msg, vbCritical, "Incorrect Value entered"
Me.Undo
End If

 

[gemma-the-husky]

change your message to display the actual values.


test the difference

if abs(val1 - val2)<0.01 then some messsage

I expect this is caused by very slight differences between the two figures.

What data types are the two controls? Exactly what values do you think they are? Is one the result of a calculation?


out of interest the safest data type for financials is CURRENCY, as they guarantee exact data up to 4 or 5 DPs.

 

[Pat Hartman]

There is an excellent article that explains this problem. You can find it at www.fmsinc.com under their articles. Look for "When Access Math Doesn't add Up". Unless you need more than four decimal places of precision, changing the datatype to currency will solve the problem.

BTW - setting the decimal place to 2 doesn't truncate/round the number. It just affects how the data is displayed. Floating point (single and double) means just that. The data type stores as many decimal places as there are.

 

[hughess7]

Thanks for all the replies - I know about the storage / display of decimals etc but these are not calculated and both values get entered in by the user (one of them admittedly into a different database but still entered manually originally). They are stored as 2 decimals I've double checked and if I use debug in the immediate windows the numbers show exactly the same eg 100.00. The reason they are not stored as currency is they could be different currencies! some are gbp, some euros etc....

 

[Brianwarnock]

They do not have to be currency to use the type currency, so what they are doesn't matter, Pat's advice is valid.

Interesting article Pat, how do you remember all of these things!

Brian

 

[hughess7]

Confused!! They are currency but not the same eg euros too. If I change the field to currency a £ is displayed in front of the value (default from my regional settings). This would be misleading to the user(?). Or I am not understanding you correctly... ?

 

[hughess7]

Thanks for the link too but I can't find the article. If I do a search for the string you suggested it comes up with a list (4 pages) of total access stuff but can't see anything obvious! Will have a proper look later I only work Tue-Thus and looking after my toddler at home today so its hard for me to get a change to look on here!!

 

[Brianwarnock]

You do not have to include a currency symbol for the currency type, it is optional.

Brian

 

[hughess7]

OK ta, I will have to look at this next week... all I did was change from number to currency field type on the table, viewed the data again and a £ was displaying at the front of the data....

 

[gemma-the-husky]

to make it clear - currency is just another data type, but does guarantee absolute precision to 4dps. you do not get that with a single or double. It does not mean the figure is actually a dollars/sterling/euros value.It's just a number representation with different precision.

again - you haven't confirmed all this - but the easiest way to test your data is to add temporary code to see what is going on.

what exactly are the values you think you have. compare them and see if they are the same. maybe one is a single and one a double, so they both show as 123.45 say when rounded to 2dps, but the actual numbers stored are slightly different. (infinitesimally different, but NOT equal)

As I say, for example you CANNOT represent 0.1 precisely using standard binary exponent/mantissa stroage techniques.

(similar to the way you cannot represent 1/3 in a decimal expansion.)

 

[Brianwarnock]

The paper is deep down ! You have to select techniques and tips then technical papers. Here is a direct link.

Brian

http://www.fmsinc.com/tpapers/math/index.html

Ps enjoy your time with the youngster, they grow up too quickly

 

[Pat Hartman]

Sorry the article was harder to find than I remembered. I don't usually post a link to it directly because I want people to see the list of other articles, all of which are worthwhile reading.

hughess7, how data is stored internally and how it is displayed are two different things. The various numeric data types have different uses and different attributes. I ran into this exact problem quite a few years ago and because of my COBOL background figured out the problem and got around it by switching to the currency data type. But since I found this article, I understand the problem a little better. Each datatype has a default format. For Currency, the default format is "currency" but you can change that to any named format. I use standard with two decimal places for most numeric fields and percent with two decimal places of some others. Ever since I switched, my floating point issues have been banished. Of course, if you need more than 4 digits of decimal precision, you can go back to single or double BUT, you also have the option to do your own scaling if you aren't going to run into trouble with significant digits. If for example, you need 6 digits for your decimal but currency only allows 4 and double gives you floating point issues, all is not lost. All you need to do is to multiply your 6 digit decimal by 100 to "move" the decimal 2 places to the left. That is "scaling". You just need to multiply and divide by 100 at various points to control how the data is stored and how it is displayed.