Validation problems 2

[Karre]

When I type a validation expression in a combo box that says that the value typed in must be between the values of two list boxes on the the same form, the validation doesn't work when there are large values in the list boxes.

If the values in the list boxes are 10 and 25 the combo box will not accepted values less than 10 or greater than 25. But if the values are 600 and 2000, the combo box only takes numbers that are OUTSIDE the range, opposite of what it should be.

Any ideas on why this is happening? Thanks.

 

[SteveR77]

Karre,

It sounds like the logic for the validation is backwards or you may be using the wrong logic in the validation itself.

>=Me![Combo1] AND <=Me![Combo2] Will cause that validation to be greater or equal to Combo1 and less than or equal to Combo2.

However, if you have >=Me![Combo1] OR <=Me![Combo2] as long as the first condition is true you may not get what you expect. Because if Combo1 is 60 and Combo2 is 100. If you put in 110 in your third combo box it will pass the validation.

A better way to get a range would be to use BETWEEN.

If you were to do BETWEEN Combo1 AND Combo2 then you'll get the entire range of numbers between Combo1 and Combo including the values of the two combo boxes.

HTH,

Steve

 

[Karre]

I am using the Between operator.

 

[SteveR77]

Karre,

I'll set up a little test database and test this. Between should work but as always there may be some unknow Microsoft gotcha that I'm not remembering when it comes to using the BETWEEN Statement.

In the meantime. Try setting up the validation using the mathematical operators of

>=[First Condition] AND <=[Second Condition]

Good luck, and I'll follow up on this one for you..

 

[SteveR77]

Karre,

This will work for you:

>=[First Condition] AND <=[Second Condition]

Where the conditions are the names for the two combo boxes where you are getting the range to validate the third. If you haven't done so add some validatation text to the third combo box so the user of your application will get feedback when the rule is not met.

B-) Steve

 

[Karre]

When I put use that condition and the value of the first list box is 500 and the value of the second is 2000, it tells me that everything is out of range, even if it's not. If works fine for small numbers, such as the range from 25 to 75, but when it gets to a large number, it sees the condition as being false, even when it's not.

 

[dneufarth]

Perhaps the values of all controls are being treated as text data types for comparison.

Convert each controls' contents to integer or long like:

=IIf(CInt([text48]) Between CInt([list44]) And CInt([list46]),&quot;TRUE&quot;,&quot;FALSE&quot


Dave

 

[SteveR77]

Karre,

I went back and doubled checked. If you did as Dave said then this will all make sense.

Here's why. 500 as a text field will be greater than 2000. This is due to the character value of 2 being less than the character value of 5.


So for text values:

1
10
10000
100000
2
20
20000000

etc.

The simplest solution would be to change the design of your table so the fields are defined as numbers and not text..

Steve