validation rule not running right with larger decimals in play

[sheckay]

I have a validation rule for a textbox that behaves differently based on the kind of numbers that are in the text boxes that provide variables for the validation rule. The rule isn't complicated. It just verifies that the user is entering a number below or equal to the plus tolerance and greater than or equal to the tolerance minus.

I noticed that it would work fine on the plus side, but not good enough on the minus side. The only problem on the minus side of the equation is that the equal to part of the greater than or equal to was not in play. Just the greater than part.

This when I have numbers like .555 and a plus tolerance of .0004 and a minus tolerance of .0004. Access accepts .5554 but not .5546. I have to put in .5547. But .5546 is equal to .555 minus .0004. Nevertheless, it won't accept it.

But if I plug in a simple number like 3 and a plus/minus tolerance of .5 the validation rule works fine. So it's not a problem with the code in the validation rule.

I've tried going into the fields of the table and formatting the fields to 5 decimal places, I did this on the form's fields too. Didn't help. Is there anything else that I can try?

The text boxes are set to number format, double.

Thanks in advance.

[Micron]

My guess is that your table field is formatted to show less characters than what is really there; i.e. the field shows .555 but contains .5551
Or there could be an issue with decimal data type that I'm unfamiliar with that might be caused by the fact that it is a floating point type of number. That is, as long as the values you've posted are close to what you're using. If there are a bunch of digits (e.g. 12 or so) to the left of decimal that you haven't mentioned, then that could be related.
You might try single instead of double data type, or research if there are issues in Access with double data type and < and > comparisons. I probably will, but it has to wait.

[CJ_London]

If you want a solution rather than guesses, provide your actual validation rule, some example data (no formats), the result you are getting from the validation rule and the result you expect. so

The text boxes are set to number format, double.

does not make sense - text boxes exist in forms and reports and do not have a number format, double. But you can set a format to display the number of characters after the dp.

Fields in tables can have a number datatatype which can be a double - and you can set a format. But they also have a decimal places property - this needs to be auto to display all the characters after the dp.

This when I have numbers like .555 and a plus tolerance of .0004 and a minus tolerance of .0004. Access accepts .5554 but not .5546.

again does not make sense to me

.5546 is not greater that .5546 so outside your tolerance, so why do you expect it to be acceptable? Perhaps you need >=

[sheckay]

My guess is that your table field is formatted to show less characters than what is really there; i.e. the field shows .555 but contains .5551
Or there could be an issue with decimal data type that I'm unfamiliar with that might be caused by the fact that it is a floating point type of number. That is, as long as the values you've posted are close to what you're using. If there are a bunch of digits (e.g. 12 or so) to the left of decimal that you haven't mentioned, then that could be related.
You might try single instead of double data type, or research if there are issues in Access with double data type and < and > comparisons. I probably will, but it has to wait.

Hello Miron,

Thank you for your reply.

The numbers I gave are the numbers that I was entering, which are giving me this problem. They're just an example to start with. And I simplified the numbers down, less decimals, to see what level the validation rule would work on.

Thank you

[sheckay]

Thank you for your reply CJ.

I'm sorry for my loose usage of terminology.

I meant that the fields of the table are set as numbers, double, and 5 decimals (just set them to that after discovering this problem). The decimal setting was at auto prior to that.

As far as the code goes, I have:

([Text165]>=[Text172]-[Text176] And [Text165]<=[Text172]+[Text174])

Text165 is the number being manually entered. Text172 is the desired number. Text174 is the tolerance plus. Text176 is the tolerance minus.

And I didn't include this because if I put simpler numbers, such as 3 for the desired number, and .5 for the +/- tolerance, the validation rule works fine. So I'm confident that the validation rule is not the problem.

Thanks again.

[sheckay]

So I continued to test things out. On the same form that I'm troubleshooting this one validation rule, there are other validation rules just like it. And, in testing them out, I found that 6 of them work fine. 2 of them are not working like they should.
So then I thought, let me plug the numbers in from the ones that are working, into the ones that aren't. Then see if the ones that weren't working now work. And also see if the ones that were working, are now not (as I plug the numbers from the ones that weren't working in them).
And that's exactly what happened.
For example, when I plug in .25 as the desired number and .0002 as the =/- tolerance, it works. Then increase the desired number to .255, still works. But when I increase the number to .555 and +/- tolerance to .0002, the same fields and same validation rule do not work like they should.
How strange is this?

[Micron]

I agree. This isn't one I'm going to dance around on with no data and no validation rule/expressions revealed. Best bet is to copy db, compact/repair copy, zip and post. As for

Perhaps you need >=

I figured that is the case because of

the equal to part of the greater than or equal to was not in play. Just the greater than part.

but am not going to continue to guess at a complex problem without seeing something.

[CJ_London]

The problem is you have provided the validation formula with very poorly named controls then describe what you are doing as if there is only one tolerance so the implication is you are using a different formula. Where is this formula? A validation property? Vba code? The controlsource to another control?

you talk about the fields of the table - so are you storing the tolerances as well? Is this calculation a calculated control?

my best guess is you are affected by bankers rounding - Google to understand more

[sheckay]

The tolerances are in fields when they are entered. They're already in the fields when this form is opened. [Text174] is the tolerance plus. [Text176] is the tolerance minus. [Text165] is the measurement taken of what is actually being measured to see if it fits within the acceptable range. [Text172] is the expected "desired" size. The acceptable range is the desired size <= + the tolerance. Or the desired size >= - the tolerance.

([Text165]>=[Text172]-[Text176] And [Text165]<=[Text172]+[Text174])

This validation rule works 100% sometimes, with certain entries. But with other entries, it acts as if the = sign is not next to the > sign, in the [Text165]>=[Text172]-[Text176] part. For example, if [Text172] is set to .25 and the +/- is set to .0002. It works. If [Text172] is changed to .555 and the +/- is still set to .0002 it doesn't accept 0.5548. It says that it's out of range. If I change that to 0.5549 it accepts it.

[CJ_London]

my best guess is you are affected by bankers rounding - Google to understand more - did you check this out? as an example in the immediate window

?0.5548>=0.555-0.0002
False


?0.5548>=0.5548
True

because bankers rounding has occurred in your 0.555-0.0002 calculation

round it

?round(0.5548,4)>=round(0.555-0.0002,4)
True

[Micron]

I still think this could also be solved by converting to single as stated in post 2.

[sheckay]

I did look into banker's rounding. But it was unclear if that was an automatic feature or something that needs to be implemented to be a factor. I'm interested in this so I could turn it off, if necessary. You could be on to something. But I don't see any numbers getting rounded up or down. No numbers are being simplified in their presentation either.
I will keep on looking into that.
Could you elaborate on the tests that you entered below? Are you suggesting that I present those in my DB to access to see what come up (either true or false)? If so, I'm not familiar with how to do that.

my best guess is you are affected by bankers rounding - Google to understand more - did you check this out? as an example in the immediate window

?0.5548>=0.555-0.0002
False


?0.5548>=0.5548
True

because bankers rounding has occurred in your 0.555-0.0002 calculation

round it

?round(0.5548,4)>=round(0.555-0.0002,4)
True

[Micron]

The rounding is taking place behind the scenes.

Could you elaborate on the tests that you entered below?

The ? indicates the tests are done in the vb editor immediate window. Further to that, if the values are single data type then
?csng(0.5548) >= csng(0.555-0.0002)
True

However, that test is not a reliable representation of using table data from a field type of single. So from a table field formatted as single, in 1st record in Return field contains .555
In query calculated field (named expr1)
0.555>=[return]-0.0002 AS expr1 (remember, [return] is the field name, record value is .555, number type is single)

returns True.

[sheckay]

Ok, I tried that. And I tested. Same result. When I enter .555 in the expected size, and .0002 in the +/- tolerance, I cannot enter .5548. I can enter .5552. It takes that. What makes this harder to understand, is that I can enter .455 into the expected with .0002 in the plus minus tolerance, it allows me to put in .4548. If I enter .655, it allows me to put in .6548. So, for some reason, .555 doesn't calculate right. I can enter .5549, but not .5548. Since .455 and .655 work fine, as do many others, I don't think there's anything wrong with the validation rule. I also don't think anything's being rounded either. If it was, that would affect .455 and .655
I know it's a a big leap, but is this a flaw in the application?

[Micron]

You might have stumbled onto something, or there could be something wrong with what you've done with form controls (assuming there are any at play) or table field design is not quite right or...
My last post was to illustrate that single data type does not exhibit rounding and the expression results in True, which is the result you want (I think).
I'm going to have to fall back to what I said previously and say that unless you post a copy of the db there doesn't seem to be anything else I can offer.