allow user to only enter values between x and y...

[guisinger]

Hi,
I need to create a validation rule that allows users of this db to enter values in the form within a certain range, BUT I want the range to be dependent of another field. For example, the user will first enter the value [Field1] and then they will try to enter a value, [Field2]. I want the user to only be allowed to enter values into Field2 that are >[Field1]-1 OR <[Field1]+2. I can't seem to make this happen. What am I doing wrong? In addition, can I create a pop-up window that shows the user exactly what error they have just made? "You must enter values >[Field1]-1 OR <[Field1]+2" would be displayed for the user.

Thanks,
Guisinger

 

[jwindon]

What types of values are in fields one and two? Integer or string or date?

 

[guisinger]

I have set the field size as single, in order that I can enter decimals. Is this my first mistake?

 

[guisinger]

I have changed the Field size to decimal, but this doesn't change my problem.

 

[jwindon]

So they are numerical values.

I set the validation property of field 2 to reference field 1 as such....

>[Text9]

What specific rule are you wishing to set on field 2?

 

[guisinger]

I want to limit to this: The length of my object has a spec. This spec. is +2/-1, so if my object length is 7, then values can range from 6 to 9. Users will enter the measured length value, but I want them to be "allowed" to enter values in this range. They would receive an error, if they enter outside of this range. The specified length spec., 7 in this example, always changes. It is entered as [Field1]. What is the formula for this, and where do I enter it? I have tried validation rule:>[Length Spec.]-1 OR <[Length Spec.]+2. This does not work. I have tried the expression builder as well.

 

[jwindon]

>=[Text0]-1 And <=[Text0]+2

Where Text0 is the numerical value of YOUR field1

If you want to add error handling, you can do this using the forms OnError event or.....just run some If-Then code on the AfterUpdate or OnChange event of field2

[This message has been edited by jwindon (edited 11-19-2001).]