validation rules

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am trying to set the following validation rule to allow only the
values listed;

<300 Or Like "mon" Or Like "tue" Or Like "wed" Or Like "thur" Or Like "fri"
Or Like "sat" Or Like "sun" Or Like "mon_*" Or Like "tue_*" Or Like "wed_*"
Or Like "thur_*" Or Like "fri_*" Or Like "sat_*" Or Like "sun_*" Or Like
"01a" Or Like "01b" Or Like "01c" Or Like "02a" Or Like "02b" Or Like "02c"
Or Like "03a" Or Like "03b" Or Like "03c"

However, when I try to test it, it works when I enter a number between 1
and 300 but when I try to enter any of the other valid data, I get a message
saying that there is a type mismatch in the form controls validation property!

Can anyone point me in the right direction, I'm really stuck on this one!

Ann Hodgetts
 
Hi, I am trying to set the following validation rule to allow only the
values listed;

<300 Or Like "mon" Or Like "tue" Or Like "wed" Or Like "thur" Or Like "fri"
Or Like "sat" Or Like "sun" Or Like "mon_*" Or Like "tue_*" Or Like "wed_*"
Or Like "thur_*" Or Like "fri_*" Or Like "sat_*" Or Like "sun_*" Or Like
"01a" Or Like "01b" Or Like "01c" Or Like "02a" Or Like "02b" Or Like "02c"
Or Like "03a" Or Like "03b" Or Like "03c"

However, when I try to test it, it works when I enter a number between 1
and 300 but when I try to enter any of the other valid data, I get a message
saying that there is a type mismatch in the form controls validation property!

Can anyone point me in the right direction, I'm really stuck on this one!
Click to expand...

What is the Data Type of the field? If it's a number, then none of the
other values will be allowed; the string "mon" is not valid in any
numeric field. And if it's Text, then <300 will not get you the
results you want: the text string "2CWQCgarbageFULL" is in fact "less
than" the text string "300" so it would pass your criterion.

Note also that your criteria using LIKE without wildcards are
unnecessary; LIKE "mon" and ="mon" are identical.

It REALLY sounds like you're trying to do something wrong with this
field, and that you might do better to have two fields (a numeric
field and a text field) and perhaps a related table containing the
valid values, with referential integrity enforced, for the text
values.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for that John.

Is there any way at all I can do this without using 2 tables. The field is
a test field. I can see where I have gone wrong with the <300 bit, I just
need the validation rule to specify that the user can only enter a 4 digit
number . I have tried to ammend the rule by using the len fuction, but again
this is not working (it now will not allow me to enter any numeric value at
all). I am using the syntax len(4). Is this correct.
 
Is there any way at all I can do this without using 2 tables. The field is
a test field. I can see where I have gone wrong with the <300 bit, I just
need the validation rule to specify that the user can only enter a 4 digit
number . I have tried to ammend the rule by using the len fuction, but again
this is not working (it now will not allow me to enter any numeric value at
all). I am using the syntax len(4). Is this correct.
Click to expand...

Let me get this straight:

This cocamamie composite field can contain these valid values:

3238
0003
mon
mon_followed by any number of characters without any constraint
thur_followed by anything the user would like to type
02a

Is that what you intend?

What Attribute of your table's Entity does this field represent? What
business purpose does the rule serve?

But to answer your question: if you want any number with fewer than
four digits to be legitimate, you can add

LIKE "#" OR LIKE "##" OR LIKE "###" OR LIKE "####"

to allow one, two, three and four digit numeric text strings
respectively.

Note that a LIKE clause without any wildcards is somewhat of a waste
of effort. It's exactly the same as an = or IN() operator. Your
condition could be:

LIKE "#" OR LIKE "##" OR LIKE "###" OR LIKE "####"
Or Like "mon_*" Or Like "tue_*" Or Like "wed_*"
Or Like "thur_*" Or Like "fri_*" Or Like "sat_*" Or Like "sun_*"
Or Like "0[123][abc]"
Or In("mon", "tue", "wed", "thur", "fri")

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top