Validation Rule

[Oorang]

I am using an input mask of ">aaaaaaaaa" for a text field that I want to only accept letters a-Z and and digits 0-9 (no spaces or symbolics). But I can't quite get the validation rule right. Any thoughts?

 

[Andrew Fergus]

Hi
Try AAAAAAAAA for 9 compulsory characters and / or digits. Change to lower case for optional.
Andrew

 

[Oorang]

Two issues I ran into with that. It allows spaces and if you use it as an input mask you cannot give a friendly error.

 

[Andrew Fergus]

The lower case 'a' allows spaces but the upper case doesn't. The lower case example means the user doesn't have to enter all of the characters - that's why you are seeing that anomaly. I agree the error messages are a bit unhelpful for the average user. If you want a variable length without spaces and custom error messages you may want to write your own validation rule using VBA on the BeforeUpdate event.

HTH, Andrew

 

[Oorang]

*sigh* That was my conclusion too. I was just hoping someone knew something that could be done at the table level. Appreciate the input

 

[Andrew Fergus]

If the first n characters are compulsory, how about something like >AAAaaaaaa? That way you won't have any spaces in the first n characters.
Andrew

 

[Oorang]

I just changed it to a fixed length all numeric field and avoided the issue all together

 

[SydneyGeek]

It's probably a bit late, but you may find this useful...

http://www.microsoft.com/mspress/books/sampchap/5054c.asp

Denis

 

[Oorang]

That IS good info (thank you), but I was already aware of it's information. It does not, unfortunatly, cover what I was trying to do. The origianl goal was to create an input mask or data validation rule that allows alphas & numerics but not symbolics or spaces while still retaining a variable length structure. I actually found a decent workaround I think with using an input mask of "aaaaaaaaa" then using the validation rule of Instr([MyField]," ") = 0. This combination seems to have accomplished the goal mostly.