A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Validation Rules



 
 
Thread Tools Display Modes
  #1  
Old February 28th, 2005, 10:09 PM
stefan
external usenet poster
 
Posts: n/a
Default 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  
Old March 1st, 2005, 12:16 AM
John Nurick
external usenet poster
 
Posts: n/a
Default

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  
Old March 1st, 2005, 01:03 AM
stefan
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Rules and Outlook 2003 Praising Jesus Installation & Setup 1 January 10th, 2005 05:01 PM
"validation rules violation" Jeff25550 Running & Setting Up Queries 2 November 30th, 2004 03:00 AM
Retrieving Email settings, rules, etc. XB Installation & Setup 3 May 18th, 2004 11:44 PM


All times are GMT +1. The time now is 11:03 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.