Validation Rules

[Guest]

Hi There,

I'm tring to create a validation rule for a field that should receive
something like:

001-30W4/2

where first group of three digits is from 001 to 126;

the secoud grop of two digits is from 01 to 30

the digit after 'W' could be only 4, 5 or 6
and the digit after '/' is from 0 to 9 except 1 (can't be 1)

I created this validation rule:

Imput Mask: 000\-00"W"0\/0;0;#

Validation Rule: Like '[0,1][0-2][0-6]-[0-2][0-9]W[4,5,6]/#'

the problem is that it takes 000 for the first group, I miss 30 for the
secoud group of digits (goes from 01 to 29)
and for the last digit after '/' it accepts 1 too

If someone could help me in creating this validation rule pattern that would
be more appreciated.

Thanks,
Stefan

 

[John Nurick]

Hi Stefan,

I doubt whether this is possible using a field-level validation rule: it
seems beyond the capabilities of the Like operator, for the reasons you
cite and also because [01][0-2][0-6] will reject many values in the
range 001 to 126.

If you do your validatoin in a form you can use a wider range of
expressions. If you install the rgxValidate function from
http://www.mvps.org/access/modules/mdl0063.htm, you can use something
like this in a textbox's BeforeUpdate event procedure:

Const REGEX = "(??:0\d\d)|(?:1[01]\d)|(?:12[0-6]))" _
& "-(??:[0-2][1-9])|(?:[123]0))W[456]\/[02-9]"

If Not rgxValidate(Me.ActiveControl.Value, REGEX) Then
Cancel = True
End If

But if the different bits of your 001-30W4/2 refer to different
attributes of whatever the whole thing is referring to, it may be better
to break it up into four separate fields each with a very simple
validation rule:

A - a number from 1 to 126
B - a number from 1 to 30
(no need to store W because it never changes)
C - A number from 4 to 6
D - A number, 0 or 2 to 9.

Hi There,

I'm tring to create a validation rule for a field that should receive
something like:

001-30W4/2

where first group of three digits is from 001 to 126;

the secoud grop of two digits is from 01 to 30

the digit after 'W' could be only 4, 5 or 6
and the digit after '/' is from 0 to 9 except 1 (can't be 1)

I created this validation rule:

Imput Mask: 000\-00"W"0\/0;0;#

Validation Rule: Like '[0,1][0-2][0-6]-[0-2][0-9]W[4,5,6]/#'

the problem is that it takes 000 for the first group, I miss 30 for the
secoud group of digits (goes from 01 to 29)
and for the last digit after '/' it accepts 1 too

If someone could help me in creating this validation rule pattern that would
be more appreciated.

Thanks,
Stefan

 

[Guest]

Thanks John,

This is very helpful because I won't lose time to solve the problem in this
way. I will do it by programming an event of a textbox or something.

Thanks,
Stefan

Hi Stefan,

I doubt whether this is possible using a field-level validation rule: it
seems beyond the capabilities of the Like operator, for the reasons you
cite and also because [01][0-2][0-6] will reject many values in the
range 001 to 126.

If you do your validatoin in a form you can use a wider range of
expressions. If you install the rgxValidate function from
http://www.mvps.org/access/modules/mdl0063.htm, you can use something
like this in a textbox's BeforeUpdate event procedure:

Const REGEX = "(??:0\d\d)|(?:1[01]\d)|(?:12[0-6]))" _
& "-(??:[0-2][1-9])|(?:[123]0))W[456]\/[02-9]"

If Not rgxValidate(Me.ActiveControl.Value, REGEX) Then
Cancel = True
End If

But if the different bits of your 001-30W4/2 refer to different
attributes of whatever the whole thing is referring to, it may be better
to break it up into four separate fields each with a very simple
validation rule:

A - a number from 1 to 126
B - a number from 1 to 30
(no need to store W because it never changes)
C - A number from 4 to 6
D - A number, 0 or 2 to 9.

Hi There,

I'm tring to create a validation rule for a field that should receive
something like:

001-30W4/2

where first group of three digits is from 001 to 126;

the secoud grop of two digits is from 01 to 30

the digit after 'W' could be only 4, 5 or 6
and the digit after '/' is from 0 to 9 except 1 (can't be 1)

I created this validation rule:

Imput Mask: 000\-00"W"0\/0;0;#

Validation Rule: Like '[0,1][0-2][0-6]-[0-2][0-9]W[4,5,6]/#'

the problem is that it takes 000 for the first group, I miss 30 for the
secoud group of digits (goes from 01 to 29)
and for the last digit after '/' it accepts 1 too

If someone could help me in creating this validation rule pattern that would
be more appreciated.

Thanks,
Stefan