Field Validation - mutliple parts

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

Guest

Hello,
I am looking to validate a field (field2) on a couple of criteria.
First, a prior drop down selection (field1) must =1, if any other option is
selected, field2 will perform normally. However, if field1 =1, then field 2
must validate the data as follows:
If string starts with a 3, it can only be 15 numeric characters (no more, no
less)
If string starts with a 4, 5, or a 6 it can only be 16 numeric characters
(no more, no less)
if criteria meant, all is well, otherwise, I plan to use the Validation Text
to notify of issue. Please help! Thanks!
 
Hi Gary,

This should be possible with a table-level (not field-level) validation
rule. (Open the table in design view and select Properties from the View
menu.)

Something like this should do the job, though it will probably need
modifying to take account of things you haven't mentioned (e.g. what
values are allowed for Field2 if Field2 is Null):

(Field1 <> 1) Or ((Field1 = 1) And (Field2 Like "3##############" Or
Field2 Like "[456]###############"))
 
Hi,



A table validation like:


(field1=1) IMP SWITCH("3"= Left(field2, 1), 15=len(field2),
Left(field2, 1) >="4" AND Left(field2,1) <="6",
16=len(field2),
true, false)


I assumed field2 is alphanumeric. I also only check if there are 15 or 16
characters, not if they are numeric.


Hoping it may help,
Vanderghast, Access MVP
 
Hi John,
Thanks for the head start, but unfortunately, I am stuck... In the table
properties for validation, I have the following line:
(Payment_Type_ID <> 2) Or ((Payment_Type_ID = 2) And (Number Like
"3##############")) - which is mirroring your below code. ( I also added
(Payment_Type_ID is null) as an option as well...) Should this be based off
of the table value or the form value?

However, when I launch form2, I get a MS Access message that it cannot
update a record due to validation errors, then followed by a pop up of "Error
'Error evaluating CHECK constraint.' in the validation rule." when I attempt
to close the form.

When I launch form2, I use an INSERT INTO command from a button on form1 to
create the record and link in another field from form1. This auto populates
the key field so it appears on form load. Any thoughts?
Thanks!


John Nurick said:
Hi Gary,

This should be possible with a table-level (not field-level) validation
rule. (Open the table in design view and select Properties from the View
menu.)

Something like this should do the job, though it will probably need
modifying to take account of things you haven't mentioned (e.g. what
values are allowed for Field2 if Field2 is Null):

(Field1 <> 1) Or ((Field1 = 1) And (Field2 Like "3##############" Or
Field2 Like "[456]###############"))

Hello,
I am looking to validate a field (field2) on a couple of criteria.
First, a prior drop down selection (field1) must =1, if any other option is
selected, field2 will perform normally. However, if field1 =1, then field 2
must validate the data as follows:
If string starts with a 3, it can only be 15 numeric characters (no more, no
less)
If string starts with a 4, 5, or a 6 it can only be 16 numeric characters
(no more, no less)
if criteria meant, all is well, otherwise, I plan to use the Validation Text
to notify of issue. Please help! Thanks!
Click to expand...
Click to expand...
 
This rule works for me and implements your initial description:

([Payment_Type_ID]<>2) Or (([Payment_Type_ID]=2) And ([Number] Like
"3##############" Or [Number] Like "[456]###############"))

You don't say why or how you added
(Payment_Type_ID is null) as an option as well...
Click to expand...

but if Payment_Type_ID is NULL the rule above does not constrain the
[Number] field.

One possible issue: what data type is the [Number] field? I've assumed
it's Text, because 16 digits is too many for the ordinary numeric field
types and you're clearly not doing arithmetic with the "numbers". If
you're using a decimal data type, (a) are you certain this is necessary?
and (b) if so, try replacing the
[Number] Like
expressions with numeric tests, e.g.
([Number] Between 4000000000000000 And 6999999999999999


Hi John,
Thanks for the head start, but unfortunately, I am stuck... In the table
properties for validation, I have the following line:
(Payment_Type_ID <> 2) Or ((Payment_Type_ID = 2) And (Number Like
"3##############")) - which is mirroring your below code. ( I also added
(Payment_Type_ID is null) as an option as well...) Should this be based off
of the table value or the form value?

However, when I launch form2, I get a MS Access message that it cannot
update a record due to validation errors, then followed by a pop up of "Error
'Error evaluating CHECK constraint.' in the validation rule." when I attempt
to close the form.

When I launch form2, I use an INSERT INTO command from a button on form1 to
create the record and link in another field from form1. This auto populates
the key field so it appears on form load. Any thoughts?
Thanks!


John Nurick said:
Hi Gary,

This should be possible with a table-level (not field-level) validation
rule. (Open the table in design view and select Properties from the View
menu.)

Something like this should do the job, though it will probably need
modifying to take account of things you haven't mentioned (e.g. what
values are allowed for Field2 if Field2 is Null):

(Field1 <> 1) Or ((Field1 = 1) And (Field2 Like "3##############" Or
Field2 Like "[456]###############"))

Hello,
I am looking to validate a field (field2) on a couple of criteria.
First, a prior drop down selection (field1) must =1, if any other option is
selected, field2 will perform normally. However, if field1 =1, then field 2
must validate the data as follows:
If string starts with a 3, it can only be 15 numeric characters (no more, no
less)
If string starts with a 4, 5, or a 6 it can only be 16 numeric characters
(no more, no less)
if criteria meant, all is well, otherwise, I plan to use the Validation Text
to notify of issue. Please help! Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Hi John,
I got it to work as well. For some reason though, I could not get it to
work as a table property and needed to insert it as a field validation - all
is well now, thank you so much!

John Nurick said:
This rule works for me and implements your initial description:

([Payment_Type_ID]<>2) Or (([Payment_Type_ID]=2) And ([Number] Like
"3##############" Or [Number] Like "[456]###############"))

You don't say why or how you added
(Payment_Type_ID is null) as an option as well...
Click to expand...

but if Payment_Type_ID is NULL the rule above does not constrain the
[Number] field.

One possible issue: what data type is the [Number] field? I've assumed
it's Text, because 16 digits is too many for the ordinary numeric field
types and you're clearly not doing arithmetic with the "numbers". If
you're using a decimal data type, (a) are you certain this is necessary?
and (b) if so, try replacing the
[Number] Like
expressions with numeric tests, e.g.
([Number] Between 4000000000000000 And 6999999999999999


Hi John,
Thanks for the head start, but unfortunately, I am stuck... In the table
properties for validation, I have the following line:
(Payment_Type_ID <> 2) Or ((Payment_Type_ID = 2) And (Number Like
"3##############")) - which is mirroring your below code. ( I also added
(Payment_Type_ID is null) as an option as well...) Should this be based off
of the table value or the form value?

However, when I launch form2, I get a MS Access message that it cannot
update a record due to validation errors, then followed by a pop up of "Error
'Error evaluating CHECK constraint.' in the validation rule." when I attempt
to close the form.

When I launch form2, I use an INSERT INTO command from a button on form1 to
create the record and link in another field from form1. This auto populates
the key field so it appears on form load. Any thoughts?
Thanks!


John Nurick said:
Hi Gary,

This should be possible with a table-level (not field-level) validation
rule. (Open the table in design view and select Properties from the View
menu.)

Something like this should do the job, though it will probably need
modifying to take account of things you haven't mentioned (e.g. what
values are allowed for Field2 if Field2 is Null):

(Field1 <> 1) Or ((Field1 = 1) And (Field2 Like "3##############" Or
Field2 Like "[456]###############"))

On Thu, 17 Aug 2006 13:06:02 -0700, Gary Dolliver

Hello,
I am looking to validate a field (field2) on a couple of criteria.
First, a prior drop down selection (field1) must =1, if any other option is
selected, field2 will perform normally. However, if field1 =1, then field 2
must validate the data as follows:
If string starts with a 3, it can only be 15 numeric characters (no more, no
less)
If string starts with a 4, 5, or a 6 it can only be 16 numeric characters
(no more, no less)
if criteria meant, all is well, otherwise, I plan to use the Validation Text
to notify of issue. Please help! Thanks!
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

validation for one field based on another 1
How to create code 2
conditional validation rules 5
Field Data help 3
Possible to link fields in a form to narrow selections? 4
IIF / OR condition (can OR be used)? 2
Requiring one field contingent on data in another field 0
Validating data 1

Back
Top