How to stop wrong dates or negative numbers in a database

Two kinds of rule stop bad entries in Base by Zaweba. A validation rule on a single field catches things like a negative quantity, and a table rule compares fields in the same record, such as a due date that must not come before the invoice date. Both carry a message in your own words, shown whenever a record breaks the rule.

That covers most of the mistakes people make when typing. The rest of this page shows how to write each kind and where they are enforced.

Rules on one field

In the table designer, give the field a rule written the way Access users will recognise. A few examples show the range: >= 0 for a price or quantity that can't be negative, Between 1 And 100 for a percentage or a score, Like "INV-*" for a reference that must start with INV-, In ("A", "B") for a short fixed set, and Is Not Null for a field that must always be filled in.

Then write the message. Make it say what to do, not just that something went wrong. "Quantity can't be less than zero" is better than "Invalid value", and "Enter a score from 1 to 100" is better still because it tells the person the range.

For dates on their own, the same approach works. A rule on a date of birth can stop dates in the future, and a rule on a booking date can keep it within a season you care about.

Rules that compare two fields

Some mistakes only show up when you look at two fields together. An end date before its start date. A due date earlier than the invoice date. A discount bigger than the price. None of those values is wrong on its own, so a field rule can't catch them.

For those, open the Rules dialog for the table and add a table rule such as [DueDate] >= [InvoiceDate], again with its own message. The square brackets name fields in the same record. This is the right home for sensible date checks and for any rule that involves two columns.

Where the rules apply

Base turns rules into CHECK constraints in the SQLite file itself. That matters because it means the rule isn't a feature of one form. The datasheet enforces it, so do forms, imports and scripts behind buttons. If you later export the database as a website, the rules become constraints in MySQL or MariaDB too, so people entering records in a browser see the same message you wrote.

Imports deserve a mention here. When you bring records in from a CSV or Excel file, rows that break the rules are skipped and listed rather than slipping in quietly, which makes a rule a useful clean-up tool as well as a guard.

Adding a rule to a table that already has records

Base checks your existing records before it accepts a new rule. If some of them already break it, the rule is refused and Base tells you how many records are at fault. That stops you ending up with a table whose rules and data disagree.

The fix is to find those records first. A quick filter in the datasheet (say, quantity less than 0) shows them, and you can correct or delete them before trying the rule again.

A few habits that help

Choose the right field type before you write a rule. A whole number or currency field already refuses words, and a date field refuses things that aren't dates, so the rule only has to deal with the range. Set a default where one is obvious, such as today for an entry date, so people have less to type and less to get wrong. For text that must follow a pattern, like a postcode, an input mask is usually kinder than a rule, because it guides people as they type instead of complaining afterwards.

If you have an AI account connected, Suggest rules can propose validation rules and input masks for the open table. You still see each one with a reason and choose which to apply, and Base still refuses any that the existing records break.