Validation Rules ?

[Carol Shu]

Hi, How to do this...
I have a table name [tblVehicle], and a field name [OdometerReading] data
type is TEXT, how to force it only allow enter 5 or 6 digit, some older
vehicle only have 5 digit odometer reading, newer car are most 6 digit.
and if the vehicle is 10 year old or older then enter "EXEMPT" in the field
[OdometerReading], i'm not sure to start with validation rules or how to do
this, please help, many thanks.

 

[Allen Browne]

Change it to a Number field.

That will be much easier than forcing users to enter a leading zero, and
guaranteeing that no non-numeric characters get into the field.

Use Null instead of Exempt. If you want to force users to enter something
for entries newer than 10 years old, you could put a validation rule on the
table (not field) of something like:
([EntryDate] < Date() - 3652) OR ([OdometerReading Is Not Null)

More about validation rules:
http://allenbrowne.com/ValidationRule.html

 

[Carol Shu]

Thank you Allen.
I tried it, and work great, but one more problem here, how do you force it
allow user enter 5 or 6 digits only? like 12345 or 123456 ? right now you can
enter like 123456789 even more, please help thanks.

Change it to a Number field.

That will be much easier than forcing users to enter a leading zero, and
guaranteeing that no non-numeric characters get into the field.

Use Null instead of Exempt. If you want to force users to enter something
for entries newer than 10 years old, you could put a validation rule on the
table (not field) of something like:
([EntryDate] < Date() - 3652) OR ([OdometerReading Is Not Null)

More about validation rules:
http://allenbrowne.com/ValidationRule.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, How to do this...
I have a table name [tblVehicle], and a field name [OdometerReading] data
type is TEXT, how to force it only allow enter 5 or 6 digit, some older
vehicle only have 5 digit odometer reading, newer car are most 6 digit.
and if the vehicle is 10 year old or older then enter "EXEMPT" in the
field
[OdometerReading], i'm not sure to start with validation rules or how to
do
this, please help, many thanks.

 

[Paul Shapiro]

([EntryDate] < Date() - 3652) OR ([OdometerReading] Is Not Null And
[OdometerReading] Between 10000 and 999999)

But you probably need to allow numbers less than 10,000? Once it's a numeric
field, you can't require users to enter leading zeroes. So maybe what you
want is just:

([EntryDate] < Date() - 3652) OR ([OdometerReading] Is Not Null And
[OdometerReading] < 999999)

Thank you Allen.
I tried it, and work great, but one more problem here, how do you force it
allow user enter 5 or 6 digits only? like 12345 or 123456 ? right now you
can
enter like 123456789 even more, please help thanks.

Change it to a Number field.

That will be much easier than forcing users to enter a leading zero, and
guaranteeing that no non-numeric characters get into the field.

Use Null instead of Exempt. If you want to force users to enter something
for entries newer than 10 years old, you could put a validation rule on
the
table (not field) of something like:
([EntryDate] < Date() - 3652) OR ([OdometerReading Is Not Null)

More about validation rules:
http://allenbrowne.com/ValidationRule.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, How to do this...
I have a table name [tblVehicle], and a field name [OdometerReading]
data
type is TEXT, how to force it only allow enter 5 or 6 digit, some older
vehicle only have 5 digit odometer reading, newer car are most 6 digit.
and if the vehicle is 10 year old or older then enter "EXEMPT" in the
field
[OdometerReading], i'm not sure to start with validation rules or how
to
do
this, please help, many thanks.

 

[Carol Shu]

Thank you Paul.
I tried both, it doesn't work. I enter like 333, it let me jump to the next
field, then i enter like 55533333, it did the same, any more suggestions,
thank you again. be sure about this, I put a validation rule on the table
(not field).

([EntryDate] < Date() - 3652) OR ([OdometerReading] Is Not Null And
[OdometerReading] Between 10000 and 999999)

But you probably need to allow numbers less than 10,000? Once it's a numeric
field, you can't require users to enter leading zeroes. So maybe what you
want is just:

([EntryDate] < Date() - 3652) OR ([OdometerReading] Is Not Null And
[OdometerReading] < 999999)

Thank you Allen.
I tried it, and work great, but one more problem here, how do you force it
allow user enter 5 or 6 digits only? like 12345 or 123456 ? right now you
can
enter like 123456789 even more, please help thanks.

Change it to a Number field.

That will be much easier than forcing users to enter a leading zero, and
guaranteeing that no non-numeric characters get into the field.

Use Null instead of Exempt. If you want to force users to enter something
for entries newer than 10 years old, you could put a validation rule on
the
table (not field) of something like:
([EntryDate] < Date() - 3652) OR ([OdometerReading Is Not Null)

More about validation rules:
http://allenbrowne.com/ValidationRule.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, How to do this...
I have a table name [tblVehicle], and a field name [OdometerReading]
data
type is TEXT, how to force it only allow enter 5 or 6 digit, some older
vehicle only have 5 digit odometer reading, newer car are most 6 digit.
and if the vehicle is 10 year old or older then enter "EXEMPT" in the
field
[OdometerReading], i'm not sure to start with validation rules or how
to
do
this, please help, many thanks.

 

[Allen Browne]

Carol, you have not applied Paul's suggestion correctly if it let you enter
333 (unless it was for a really old date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Paul.
I tried both, it doesn't work. I enter like 333, it let me jump to the
next
field, then i enter like 55533333, it did the same, any more suggestions,
thank you again. be sure about this, I put a validation rule on the table
(not field).

([EntryDate] < Date() - 3652) OR ([OdometerReading] Is Not Null And
[OdometerReading] Between 10000 and 999999)

But you probably need to allow numbers less than 10,000? Once it's a
numeric
field, you can't require users to enter leading zeroes. So maybe what you
want is just:

([EntryDate] < Date() - 3652) OR ([OdometerReading] Is Not Null And
[OdometerReading] < 999999)