If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Validation Rules
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 |
#2
|
|||
|
|||
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 procedu 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. On Mon, 28 Feb 2005 13:09:03 -0800, stefan wrote: 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 [Microsoft Access MVP] Please respond in the newgroup and not by email. |
#3
|
|||
|
|||
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 "John Nurick" wrote: 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 procedu 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. On Mon, 28 Feb 2005 13:09:03 -0800, stefan wrote: 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 [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Rules and Outlook 2003 | Praising Jesus | Installation & Setup | 1 | January 10th, 2005 04:01 PM |
"validation rules violation" | Jeff25550 | Running & Setting Up Queries | 2 | November 30th, 2004 02:00 AM |
Retrieving Email settings, rules, etc. | XB | Installation & Setup | 3 | May 18th, 2004 11:44 PM |