Validation Rule problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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!
Click to expand...

Try:

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

James A. Fortune
(e-mail address removed)
 
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
 
Using the Form you can write
code in the OnChange event of the relevant Controls.
Click to expand...

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

John W. Vinson [MVP]
 
Thanks John. Cognator misfired.

--
-Larry-
--

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

John W. Vinson [MVP]
Click to expand...
 
I may try my hand at that, as well. Thanks!

Regina

Larry Daugherty said:
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-
--

RRozsa said:
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!
Click to expand...
Click to expand...
 
Worked like a charm! Thanks so much!

Regina

James A. Fortune said:
RRozsa said:
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!
Click to expand...

Try:

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

James A. Fortune
(e-mail address removed)
Click to expand...
 
Back
Top