0

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...

  • 1
    Those validation rules work for my text field in an ACCDB format database using Access 2010, 32 bit with all Office and Windows updates applied. So I don't understand why they're not working for you. I would try compact & repair, but not confident that is the answer. – HansUp Jan 11 '16 at 16:11
  • 1
    I would recommend to validate in `BeforeUpdate` event of form instead of table level validation, it will give you more flexibility – Sergey S. Jan 11 '16 at 16:54
  • Thank you both for your replies. This has continued to happen sporadically, whether in the form or table. I have tried Compact & Repair and will see if that helps. It does seem to have fixed it... but only time will tell. One last thought -- some records in the table have invalid data for these fields. Could that cause any issues? –  Feb 09 '16 at 19:12
  • HansUp, it is possible that your suggestion has resolved the issue. Last week I ran compact & repair, and no errors have come up since then. I am going to keep an eye on it in the next week or so, and will consider it resolved if all goes well. Would you be willing to repost your comment as an answer so I can accept it in such case? –  Feb 15 '16 at 19:56
  • @HansUp, I can confirm that the issue was resolved, and it does seem it was due to your solution. I would like to give you proper credit, so please do post an answer when you have a moment! –  Apr 19 '16 at 14:24
  • Ah, okay... fair enough! I shall submit an answer then. Thank you again for your help, all the same. –  Apr 20 '16 at 17:15

1 Answers1

1

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.