Validation with an IIF Statement

[Guest]

I am creating an IIF Statement in the Validation Rule property of a field
(Field A). I need it to provide the Validation Text based on a calculation in
another field (Field B). If [Field B] is between 0.01 and 0.99, then give the
Validation Text, if not then do nothing and let the users move on.

This is one of the many statements I've tried:
=IIf([Field B] Between 0.01 And 0.99,Between 0.01 And 0.99,0 Or 1)


Regards,
~ Chris

 

[Guest]

Hi, Chris.

I need it to provide the Validation Text based on a calculation in
another field (Field B).

Sorry. You can't. The column's validation rule only applies to _that_
column. If you need a validation rule on FieldB, then place the following in
FieldB's Validation Rule Property:

[FieldB] Not Between 0.01 And 0.99

However, if you want to see whether or not FieldB has incorrect values
already (which I suspect is why you want to place the Validation Rule on
another column), then use a query such as the following:

SELECT FieldB, IIF(FieldB Not Between 0.01 And 0.99, NULL, "Bad Data") AS
CheckFieldB
FROM MyTable;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I am creating an IIF Statement in the Validation Rule property of a field
(Field A). I need it to provide the Validation Text based on a calculation in
another field (Field B). If [Field B] is between 0.01 and 0.99, then give the
Validation Text, if not then do nothing and let the users move on.

This is one of the many statements I've tried:
=IIf([Field B] Between 0.01 And 0.99,Between 0.01 And 0.99,0 Or 1)


Regards,
~ Chris