Validation Rule problem

R

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!
 
J

James A. Fortune

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!

Try:

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

James A. Fortune
(e-mail address removed)
 
L

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
 
J

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]
 
L

Larry Daugherty

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]
 
R

RRozsa

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!
 
R

RRozsa

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!

Try:

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

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top