0

How do I add a validation rule to a column in MS Access 2013, where the column value can either be "A" or "*"? When I try to add the validation rule:

"A" Or "*"

The MS Access Expression Builder changes it to:

"A" Or Like "*"

Which doesn't constrain the value at all. I have tried the following, and all are modified by the expression builder to include the word "Like":

"A" Or "*"
'A' Or '*'
"A" Or "\*"

I have been able to trick Access into treating my text as a string literal using the following kludge:

"A" Or UCase("*")

But that seems incredibly cumbersome to do something so simple. Does anyone know the "clean" way to add such a validation rule to Access 2013?

Jeff G
  • 4,470
  • 2
  • 41
  • 76
  • Try "A" Or "~*" I know that's how you specify the astrix in Excel but have not tested with access. – Sorceri Jan 04 '16 at 18:22
  • The expression builder still adds the word "Like" before that, so it becomes `"A" Or Like "~*"`. Any string with an asterix in it appears to be treated as if it is a "Like" expression. – Jeff G Jan 04 '16 at 18:24
  • 1
    You will need `UCase` in case(!) you wish to invalidate "a". – Gustav Jan 05 '16 at 07:11
  • @HansUp That is a great suggestion, but I would recommend using `'A' Or Chr(Asc('*'))` instead to improve readability. – Jeff G Jan 05 '16 at 18:58
  • @Gustav The fact that Access treats set membership as case insensitive is incredibly frustrating. One of the many quirks about Access that will forever make me recommend against its use as a DBMS. – Jeff G Jan 05 '16 at 19:04
  • Readable by whom, Jeff? Surely not the end users? They should see the message you provide in *Validation Text*. – HansUp Jan 05 '16 at 19:29
  • @HansUp More readable to the other developers on my team, who probably won't immediately realize that 42 is the ASCII code for asterix. In my example, such a developer could read the validation text and make an educated inference that "*" == Chr(42), but that breaks down the instant someone decides to copy/paste the same code for some other ASCII symbol. Thereafter, everyone who has to modify the validation rule must search for the ASCII code table, when the extra obfuscation could have been easily avoided. – Jeff G Jan 05 '16 at 21:52
  • This problem (Access inserts `Like` contrary to your wishes) only applies for `*` and `?` characters. For any other character, **don't** use `Chr()` ... just type the character. If you're still concerned that some day you may want `?` allowed in that field, your developers need not search ASCII table ... just flip open Immediate window and use `? Asc("?")` This is nowhere near as challenging as you made it out to be. Despite that, I think `In("A","*")` is the better choice for you. – HansUp Jan 07 '16 at 18:17

1 Answers1

1

Access supports an "In" operator, which would allow for the following validation rule syntax:

In("A","*")

If the column also supports null values, then simply add that exception before the "In" operator:

Is Null Or In("A", "*")

Jeff G
  • 4,470
  • 2
  • 41
  • 76