Question Validation Rule

[TIMER]

Hi,

Access newbie here. Just finishing a beginners course on Data Application Management and I need help with 2 validation rules I'm trying to complete for a course lab report.

The 1st validation rule I need to create, is one where the text characters are less than 4 in length ie. Miss, Mr, Dr, Mrs (Title field name). Its easy with numbers but I can't seem to complete the task with (a-z) text/letters/characters. I know that I can limit the Field Size to 4 but the purpose of the lab is to try and use the validation rule to set it up this task.

The 2nd validation rule I require is one where the EmployeeID field name require only 8 characters, first character begin with a Letter and the other 7 characters are digits ie E1234567. Again I know that input mask can do this job but I'm required to get create a validation rule to complete this task.

Hope someone can help. Please & Thanks

 

[Peter Reid]

For the first one (although there's probably a better solution);

Like "????" Or Like "???" Or Like "??" Or Like "?"

 

[TIMER]

Brilliant, that one works. Thanks a mil Peter.

 

[TIMER]

I think I have solved the 2nd Validation Rule

Like "E#######"

But I have decided to use the letter "E" as the first Character and Letter of the required validation rule and using the character # to allow entry of a number only data.

What I seem to have a problem is with the letters. I don't seem to have the choice of entering any other letter in table view like I would with a Input Mask

# - seem to be for NUMBERs only
? - seem to be for TEXT and NUMBERs only
.....and nothing for TEXT only, but I may be wrong if someone could enlighten me.

 

[Peter Reid]

According to http://allenbrowne.com/ValidationRule.html, the following might work for the second one

Like "[!a-z]#######"

 

[Poppa Smurf]

The 1st validation rule I need to create, is one where the text characters are less than 4 in length ie. Miss, Mr, Dr, Mrs (Title field name). Its easy with numbers but I can't seem to complete the task with (a-z) text/letters/characters. I know that I can limit the Field Size to 4 but the purpose of the lab is to try and use the validation rule to set it up this task.

The 2nd validation rule I require is one where the EmployeeID field name require only 8 characters, first character begin with a Letter and the other 7 characters are digits ie E1234567. Again I know that input mask can do this job but I'm required to get create a validation rule to complete this task.

The following is a solution to your posting, there maybe a more simplier method.

1st Validation - Length for ie. Miss, Mr, Dr, Mrs (Title field name).
Use the Len() function


2nd Validation E1234567
1. Use the Len() function to check for 8 characters.
2. If it is 8 characters, use the Left() or MID() function to obtain the first character of the string in this case the E
3. Convert result of the first letter from 2 to uppercase using Ucase()
4. Use the Instr() function to check that the first character is a ltter of the alphabet.
5. If it is a valid character then use the Mid() function to obtain the remaining values of the characters
6. Use the Val() function to obtain the numerical value of the string obtained in step 5
7. If the result of the Val is > 0 then it is valid.

The above steps assumes the numbers are not all zeroes e.g. E0000000

Have a go using the above and if you are having problems I will post the code.

 

[TIMER]

Peter your link http://allenbrowne.com/ValidationRule.html provided great help and the suggestion you posted for the 2nd Validation Rule was extremely close.

The solution to Validation No 2 I found by playing around with your suggestion was...

Like "[a-z]#######"

As you can see I just dropped the ! in the rule and result it works brilliantly

Thanks once again for your help and time.

Poppa Smurf, I'm going to try your solution later and see if that works but the above rule is the simplest as far as I can make out.

Thank you all for your assistance. I have a much larger project to do next involving a BOM (Bill of Material) scenario which I'm dreading.

 

[TIMER]

I played around with that website you suggested Peter and found another more secure solution to Validation No 1

Like "[a-z]" Or Like "[a-z][a-z]" Or Like "[a-z][a-z][a-z]]" Or Like "[a-z][a-z][a-z][a-z]"

A bit long winded but it prevents the end user from possibly entering in digits into the Title field.

Thanks again for the inspiration.