Validation Rule

[semaj21]

Hello can anyone help me I am trying to make a validation rule for a field. It needs to be Between 0 And 28 OR "NA". When I use this in the validation property it works for the numbers and text but if you input say NJ or any other combination it issues a message box saying cannot evaluate the constraint, check Validation rules.
Am I missing some important syntax or something. Any help gratefully appreciated.
Barry

 

[boblarson]

Is your field's datatype TEXT?

 

[semaj21]

Yes it is Text

 

[boblarson]

Then you have to include the numbers in quotation marks as well.

 

[semaj21]

Thanks for your help.
Does that mean I would have to type in say LIKE "NA" OR "0" OR "1" OR "2" OR "3" and so on or do you know of a simpler way to do it.
Regards Barry

 

[boblarson]

You should be able to do this:

"NA" Or Between "0" And "28"

 

[boblarson]

And I forgot to tell you - You don't need the word LIKE

 

[semaj21]

Thanks works a treat
Barry

 

[juliet123]

i tried to do this in my database and it won't work...my field is also set to text....but the validation rule i want is between 7 to 13....
can this only be done in a query or is there also a way to do it in a table? pls help

 

[boblarson]

i tried to do this in my database and it won't work...my field is also set to text....but the validation rule i want is between 7 to 13....
can this only be done in a query or is there also a way to do it in a table? pls help

This WAS set in the validation rule in the field's validation property in the table. Are you using:

Between "7" And "13"

with the correct location of the quotation marks?

 

[semaj21]

Sorry BOB but there is a problem when you enter the numbers between say 4 to 9 ACCESS returns an error, it recognises all the other numbers as correct. I entered the rule as you said i.e "NA" Or Between "0" And "28". The only way to get it to work for me was to type it in as, LIKE "NA" OR "0" OR "1" OR "2" OR "3" and so on. It will work fine then.

 

[boblarson]

I'd like to see your database then because I actually did it and it worked great.

 

[juliet123]

yeh thatz what happened to mine as well...

wen i put Between "7" And "13" it lets me enter numbers below 7 and above 13....

 

[neileg]

There's an issue here about how text is sorted. If you store numbers as text, they don't sort like numbers. So you get:
1
13
112
21
234
7
777
etc.
So BETWEEN "7" AND "13" doesn't mean what you think it means.

Remember that Access does what you tell it to do, not what you want it to do...

 

[Rabbie]

Just tried it in Access 2003.

Validation rule was Between "7" and "13"

Error only given when I input 1. everything else was accepted.

Just seen Neileq's explanation after I had posted so ignore this.

 

[juliet123]

so whats the validation rule then ? sorry, am confused

 

[neileg]

Either change the field to numeric and use Between 7 and 13
Or leave it as text and use In("7","8",......"13")

 

[juliet123]

Either change the field to numeric and use Between 7 and 13
Or leave it as text and use In("7","8",......"13")

even if the field is numeric and i use that expression "between..." it doesnt work.... as for that expression of In(....), it doesn'twork....it says something about the character string being too long of something....

But expression "7" Or "8" Or "9" ..........etc does work ... so il just use this one instead....

 

[neileg]

even if the field is numeric and i use that expression "between..." it doesnt work.... as for that expression of In(....), it doesn'twork....it says something about the character string being too long of something....

But expression "7" Or "8" Or "9" ..........etc does work ... so il just use this one instead....

Strange, I've just set up a dummy database and tried both of these ideas. They both work!