Validation Rule problem

[RRozsa]

Hi, everyone...

I know you are sick to death of newbies' validation rules questions, but....

In my database table I have two fields, both (text) list boxes. The first
field, called [InquiryStatusBox], has three values: "Implemented",
"Slippage", or "Not Yet Due". The second field, [SlippageBox], has three
values: null (default), "With Merit", and "Without Merit". I'm trying to
set up a validation rule for the [SlippageBox] field that says if a user
selects anything OTHER than "Slippage" in the [InquiryStatusBox] field, they
have to leave the [SlippageBox] field blank. If the user selects "Slippage"
in the [InquiryStatusBox] field, they must either select "With Merit" or
"Without Merit" in the [SlippageBox] field.

When I'm testing my validation rule, it works fine if I select, say,
"Implemented" in the [InquiryStatusBox] field and then try to select "With
Merit" in the [SlippageBox] field -- it gives me an error that says I have an
invalid input. But if I select "Slippage" in the [InquiryStatusBox] field,
and then select "With Merit" in the [SlippageBox] field, instead of accepting
the value as valid, it gives me a "type mismatch in the ValidationRule
property" error.

Here is my validation rule, entered in the [SlippageBox] field:

=IIf([InquiryStatusBox]="Slippage",[SlippageBox]="With Merit" Or
[SlippageBox]="Without Merit",[SlippageBox]="")

Can anyone see what the problem might be? I've tried substituting single
quotes for double quotes, or putting a space between the double quotes in the
last argument, as well as in the default value of the field, but I'm still
getting the error.

TIA!

 

[James A. Fortune]

Hi, everyone...

I know you are sick to death of newbies' validation rules questions, but....

In my database table I have two fields, both (text) list boxes. The first
field, called [InquiryStatusBox], has three values: "Implemented",
"Slippage", or "Not Yet Due". The second field, [SlippageBox], has three
values: null (default), "With Merit", and "Without Merit". I'm trying to
set up a validation rule for the [SlippageBox] field that says if a user
selects anything OTHER than "Slippage" in the [InquiryStatusBox] field, they
have to leave the [SlippageBox] field blank. If the user selects "Slippage"
in the [InquiryStatusBox] field, they must either select "With Merit" or
"Without Merit" in the [SlippageBox] field.

When I'm testing my validation rule, it works fine if I select, say,
"Implemented" in the [InquiryStatusBox] field and then try to select "With
Merit" in the [SlippageBox] field -- it gives me an error that says I have an
invalid input. But if I select "Slippage" in the [InquiryStatusBox] field,
and then select "With Merit" in the [SlippageBox] field, instead of accepting
the value as valid, it gives me a "type mismatch in the ValidationRule
property" error.

Here is my validation rule, entered in the [SlippageBox] field:

=IIf([InquiryStatusBox]="Slippage",[SlippageBox]="With Merit" Or
[SlippageBox]="Without Merit",[SlippageBox]="")

Can anyone see what the problem might be? I've tried substituting single
quotes for double quotes, or putting a space between the double quotes in the
last argument, as well as in the default value of the field, but I'm still
getting the error.

TIA!

Try:

=IIf([InquiryStatusBox]="Slippage","With Merit","") Or
IIf([InquiryStatusBox]="Slippage","Without Merit","")

James A. Fortune
(e-mail address removed)

 

[Larry Daugherty]

It would be a more doable thing and a lot friendlier to your users to
work out the *business rules* on a Form. Using the Form you can write
code in the OnChange event of the relevant Controls. You could even
change the instructions visible on the form depending on what the user
did last. In cases where the last change of content requires that the
contents of a field be a certain value, you can set that value with
code. The data in the various controls will later be written to the
fields in the record.

Access tables aren't *active cells* like Excel. You can't store
complex, active "code" or triggers in the tables. At best a
validation error can be recognized only when there is an attempt to
save the new/modified record. Then the validation rule will yell at
the user who may have entered or altered content in many fields since
touching the one that caused the validation rule to fire.

HTH

 

[John W. Vinson]

Using the Form you can write
code in the OnChange event of the relevant Controls.

nitpick: AfterUpdate event. The Change event fires with every keystroke;
AfterUpdate when a new value is entered.

John W. Vinson [MVP]

 

[Larry Daugherty]

Thanks John. Cognator misfired.

--
-Larry-
--

nitpick: AfterUpdate event. The Change event fires with every keystroke;
AfterUpdate when a new value is entered.

John W. Vinson [MVP]

 

[RRozsa]

I may try my hand at that, as well. Thanks!

Regina

It would be a more doable thing and a lot friendlier to your users to
work out the *business rules* on a Form. Using the Form you can write
code in the OnChange event of the relevant Controls. You could even
change the instructions visible on the form depending on what the user
did last. In cases where the last change of content requires that the
contents of a field be a certain value, you can set that value with
code. The data in the various controls will later be written to the
fields in the record.

Access tables aren't *active cells* like Excel. You can't store
complex, active "code" or triggers in the tables. At best a
validation error can be recognized only when there is an attempt to
save the new/modified record. Then the validation rule will yell at
the user who may have entered or altered content in many fields since
touching the one that caused the validation rule to fire.

HTH
--
-Larry-
--

Hi, everyone...

I know you are sick to death of newbies' validation rules questions, but....

In my database table I have two fields, both (text) list boxes. The first
field, called [InquiryStatusBox], has three values: "Implemented",
"Slippage", or "Not Yet Due". The second field, [SlippageBox], has three
values: null (default), "With Merit", and "Without Merit". I'm trying to
set up a validation rule for the [SlippageBox] field that says if a user
selects anything OTHER than "Slippage" in the [InquiryStatusBox] field, they
have to leave the [SlippageBox] field blank. If the user selects "Slippage"
in the [InquiryStatusBox] field, they must either select "With Merit" or
"Without Merit" in the [SlippageBox] field.

When I'm testing my validation rule, it works fine if I select, say,
"Implemented" in the [InquiryStatusBox] field and then try to select "With
Merit" in the [SlippageBox] field -- it gives me an error that says I have an
invalid input. But if I select "Slippage" in the [InquiryStatusBox] field,
and then select "With Merit" in the [SlippageBox] field, instead of accepting
the value as valid, it gives me a "type mismatch in the ValidationRule
property" error.

Here is my validation rule, entered in the [SlippageBox] field:

=IIf([InquiryStatusBox]="Slippage",[SlippageBox]="With Merit" Or
[SlippageBox]="Without Merit",[SlippageBox]="")

Can anyone see what the problem might be? I've tried substituting single
quotes for double quotes, or putting a space between the double quotes in the
last argument, as well as in the default value of the field, but I'm still
getting the error.

TIA!

 

[RRozsa]

Worked like a charm! Thanks so much!

Regina

Hi, everyone...

I know you are sick to death of newbies' validation rules questions, but....

In my database table I have two fields, both (text) list boxes. The first
field, called [InquiryStatusBox], has three values: "Implemented",
"Slippage", or "Not Yet Due". The second field, [SlippageBox], has three
values: null (default), "With Merit", and "Without Merit". I'm trying to
set up a validation rule for the [SlippageBox] field that says if a user
selects anything OTHER than "Slippage" in the [InquiryStatusBox] field, they
have to leave the [SlippageBox] field blank. If the user selects "Slippage"
in the [InquiryStatusBox] field, they must either select "With Merit" or
"Without Merit" in the [SlippageBox] field.

When I'm testing my validation rule, it works fine if I select, say,
"Implemented" in the [InquiryStatusBox] field and then try to select "With
Merit" in the [SlippageBox] field -- it gives me an error that says I have an
invalid input. But if I select "Slippage" in the [InquiryStatusBox] field,
and then select "With Merit" in the [SlippageBox] field, instead of accepting
the value as valid, it gives me a "type mismatch in the ValidationRule
property" error.

Here is my validation rule, entered in the [SlippageBox] field:

=IIf([InquiryStatusBox]="Slippage",[SlippageBox]="With Merit" Or
[SlippageBox]="Without Merit",[SlippageBox]="")

Can anyone see what the problem might be? I've tried substituting single
quotes for double quotes, or putting a space between the double quotes in the
last argument, as well as in the default value of the field, but I'm still
getting the error.

TIA!

Try:

=IIf([InquiryStatusBox]="Slippage","With Merit","") Or
IIf([InquiryStatusBox]="Slippage","Without Merit","")

James A. Fortune
(e-mail address removed)