Multiple Validation Expressions

[RanaeB]

I want to setup a field with two validation rules, but am not having any luck.

For example, I want a number field to only accept numbers greater or equal
to 80000 and be six digits in length. So, I set the validation rules to be:

=80000 Like "######"

But, when I enter 080001 for example, it says the validation rules have been
violated.

Is it the leading zero? If so, how can it be changed to accept leading
zeros? Is there another way to string together validation rules? I tried
inserting an "AND" in between the two, but that didn't work either. Is the
Like "######" an incorrect expression for limiting digit length?

I have several other fields formatted as decimals (in order to prevent
rounding) which don't seem to work with the "Like" function either. But, I
need someway to require only a certain number of digits, no more and no less,
will be accepted.
Any help is appreciated!

 

[Ken Sheridan]

If you want the leading zero stored in the column than it will have to be of
text data type.

Instead of using a validation Rule you could put the following in the
BeforeUpdate event procedure of a control on a form bound to the column:

Const conMESSAGE = _
"Value must be greater than 080000 and of 6 digits"
Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Len(ctrl) <> 6 Or Val(ctrl) < 80000 Then
MsgBox conMESSAGE, vbExclamation, "Invlid Value"
Cancel = True
End If

Ken Sheridan
Stafford, England

Alternatively you could use a number data type and format the column as
"000000" in which case a Validation Rule of 'Between 80000 And 99999' would
suffice.

 

[Ken Sheridan]

That should have been:

Between 80000 And 999999

If you want the leading zero stored in the column than it will have to be of
text data type.

Instead of using a validation Rule you could put the following in the
BeforeUpdate event procedure of a control on a form bound to the column:

Const conMESSAGE = _
"Value must be greater than 080000 and of 6 digits"
Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Len(ctrl) <> 6 Or Val(ctrl) < 80000 Then
MsgBox conMESSAGE, vbExclamation, "Invlid Value"
Cancel = True
End If

Ken Sheridan
Stafford, England

Alternatively you could use a number data type and format the column as
"000000" in which case a Validation Rule of 'Between 80000 And 99999' would
suffice.

 

[RanaeB]

Thanks for your reply. I have a few clarification/follow-up questions.

Storing leading zeros is not a big concern for me unless for some reason it
is effecting how validation rules have to be constructed. Is this correct?

I tried your alternative solution which does allow all of the numbers I want
to capture (080000 - 9999999), but doesn't set a specific number of digits
which have to be entered. I was using Like "######" as a validation rule to
ensure that digits were not dropped or added during data entry. But, when I
tried inserting 000000 as a format, it doesn't catch when I miss a digit and
only input a five digit number instead of six.

Do you have information on how to ensure that a specific a number of digits
are entered? I like using the validation rule for this b/c it will alert
someone immediately when something isn't entered in an acceptable format. If
there is another way to alert and set a number of digits other than using a
validation rule, please let me know.

I found that when I entered both restrictions (an expression as Between
80000 And 999999 and an expression to limit the number of digits), almost
nothing I entered was accepted. Whereas, either expression entered on it's
own as a validation rule, worked fine. This made me wonder if there were
rules for entering mutiple expressions as a validation rule. Is this
correct, and if so, do you know how they should be formatted?

My Access experience is quite limited, I hope this explanation is clear
enough.
Thanks!

 

[John Spencer]

Pardon me, but I would change the field type to text and use a
validation rule similar to the following

Like "######" And Val(SomeField) Between 80000 and 999999

If you need the leading zeroes and don't plan to do any math on the
field, then a text field makes more sense for storing the data.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[RanaeB]

I just made the changes, and tried to enter both 080000 and 080001, neither
of which was accepted. My validation text pops up and then another one right
behind it saying the data violates the validation rule. Am I missing
something?

Should the validation rule be entered word for word as is written below?

Thanks.

 

[John Spencer]

Thevalidation rule should read something like the following. I forgot to
handle Null (blank entry) and to make sure the field name was surrounded with
brackets so it would not get converted to a string.

Like "######" And Val([SomeField] & "") Between 80000 and 999999

If you have the field set as REQUIRED in table design, you may be triggering a
second message based on what you have in the field's properties. In other
words, I'm not sure why you would be getting the second message.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[RanaeB]

It worked! Thank you!

The field wasn't required in the table design, but I haven't received the
second error message again, so it's not an issue now.

Last question. Based on the validation rule below, to have multiple
validation rules you add:
And Val([field name] & "")
between the two expressions, is that correct?

Thanks.

Thevalidation rule should read something like the following. I forgot to
handle Null (blank entry) and to make sure the field name was surrounded with
brackets so it would not get converted to a string.

Like "######" And Val([SomeField] & "") Between 80000 and 999999

If you have the field set as REQUIRED in table design, you may be triggering a
second message based on what you have in the field's properties. In other
words, I'm not sure why you would be getting the second message.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I just made the changes, and tried to enter both 080000 and 080001, neither
of which was accepted. My validation text pops up and then another one right
behind it saying the data violates the validation rule. Am I missing
something?

Should the validation rule be entered word for word as is written below?

Thanks.

 

[Ken Sheridan]

Using a number data type makes things very simple as all you have to validate
is that the value is within the permitted numeric range:

Between 80000 And 999999

If you then format the value as 000000 (6 zeros) whether the user enters the
leading zero for values below 080000 and 099999 is irrelevant as the number
will be displayed with the leading zero once entered (I do this with my own
accounts in Excel for cheque numbers and have gotten into the habit of
entering them without the leading zeros which appear on the cheques, which
are then added by the formatting). You can format it in the table design and
in controls in a report or form, and in a column returned by a query. Its
important to understand, however, that the actual values of the data are
numeric values, not strings with a leading zero where necessary. A numeric
value can be expressed however you wish. Conventionally we use decimal
notation most of the time, but whatever notation is used the underlying value
is always the same, e.g. 81234 in decimal notation and 13D52 in hexadecimal
notation are the same numeric value. If you enter 081234 its still the same
number.

When querying on the basis of the number the expression can be expressed in
various ways, for instance:

WHERE [MyField] = 81234

or as:

WHERE [MyField] = 081234

or as

WHERE ([MyField],"000000") = "081234"

The argument you'll sometimes see put forward that a field should only be a
number data type if there is a requirement for arithmetical operations to be
carried out on the values is unconvincing. A number might well have ordinal
or cardinal significance without any requirement for arithmetical operations
to be carried out on it, in which case it is undoubtedly a number and how its
expressed is irrelevant. Where a 'number' is in fact an encoding system,
however, then a text data type is preferable because how it is expressed is
part of the encoding system, e.g. leading zeros can often play a part in the
encoding system. Telephone numbers, Zip codes and ISBN numbers are examples
of encoding systems. Telephone numbers are a good example of the importance
of leading zeros in an encoding system; 00 are the first two digits of the
international code (though a + sign can, and often is, used in place of the
double zero), and in the UK all area and other codes begin with zero.

Ken Sheridan
Stafford, England

 

[John Spencer]

You can only have ONE validation rule. If you want multiple conditions you
need to use the boolean operators (and or) to chain them. The validation rule
returns true or false and if it returns false the validation gets triggered.

So if you want to allow A or null the validation rule looks like
IS Null OR = "A"
Obviously
IS NULL AND = "A"
will always fail. Since a value cannot be both Null and equal to "A" at the
same time.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County