I am using some simple validation rules on a table in the database I manage (it is ANSI-89 at the moment, if that helps). One, for example, reads:
Like "#" Or Like "##"
As I understand it, this should allow any single- or double-digit number (1, 2, 35, 00, 99, et cetera). However, typing "1" into the field is rejected, and the validation rule keeps prompting for a correct input, as it is a required field.
Similarly, and more importantly, I have another field that is validated like so:
Like "######?"
"201620A" should be valid (as you may guess data for this field is based partially on the year). And, while not real data, "123456Z" should be accepted as well. Despite this, both are rejected.
Because they are required fields, I am then unable to save the record... usually. Bizarrely, I have sometimes been able to save the record successfully. I.e., it's not behaving consistently.
I am baffled. I wish to retain the validation rules, as these fields are essential and I would like to at least do some basic checking to ensure they have been entered correctly. I realize there must be some simple thing I am overlooking...
As noted in the comments, HansUp's suggestion of using Compact & Repair seems to have corrected the issue, as once I did that the problem has been consistently gone now for over two months. While it is a simple process, in case anyone needs it Microsoft's instructions on how to do this can be found here: https://support.office.com/en-us/article/Compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2?ui=en-US&rs=en-US&ad=US&fromAR=1
They suggest backing up the database first. Here is an excerpt:
Compact and repair a database that you have open
NOTE: If other users are also currently using the database file, you cannot perform a compact and repair operation.
On the File tab, click Info, and then click Compact and Repair Database.
Compact and repair a database that is not open
NOTE: If other users are currently using the database file, you cannot perform a compact and repair operation. While you run the compact and repair operation, no one can use the database file.
Start Access, but do not open a database.
Point to Info, and then click Compact and Repair Database.
In the Database to Compact From dialog box, navigate to and double-click the database that you want to compact and repair.