Puzzling validation rule for text only

[bwelton]

Hi

I'm confused about the Access validation rule for text only fields. All the support states to use:



Not Like "*[!a-z]*"

This is difficult for the students I teach to understand.

I tried to teach them:

Like "*[a-z]*"

instead, which should be logically equivalent and easier to grasp. However, this does not work for a mixture of letters and numbers i.e. Jo6n. The first version of the rule does catch this and correctly reports an error. But why is this so?

Thanks
Bryan

[ItsMe]

Can you explain what the following is accomplishing and where it is applied?
Not Like "*[!a-z]*"

I am having difficulty placing it anywhere specific and understanding its function.

[bwelton]

It's a validation rule for a field in a table. It's used to ensure that there can be no numbers or symbols in a forename, surname field etc.

[ItsMe]

OK, thanks. As a constraint at the table level, Not Like "*[!a-z]*", is probably the only way to go.

I will be perfectly honest and say that I have not used the brackets and the bang together like that before. But, if your question is about a simple way to do it and the confusion about using the Not operator. I don't see a way around it.

I find that expressions can be difficult to understand if they are not used within a statement. So using an If Then Else statement might help teach others about how the Not operator can replace = False.

[ItsMe]

I did some testing and, as a result, removed my edit from post #4. What I found is that the expression in quotes is evaluated separately. So if the rule is to not have any numbers, you need to evaluate each char using the bang. Then, evaluate each result using the Not operator. In other words, the only way to ensure the rule that no char is numeric, you need both the ! and the Not operators. Otherwise, a combination of numeric and alpha chars will be allowed.