If you’ve gone through the basics of MS Access — creating tables, running queries, understanding relationships — you might feel like you have a handle on things. But there’s a whole layer underneath that most beginners skip: table rules and validations.
This is what separates a database that works from a database that works reliably. In this Level-2 guide (based on the Skill Header tutorial), we’ll walk through 14 practical techniques that help you control exactly what data gets into your tables — and how it looks when it gets there.
Why Validations Matter
Bad data is worse than no data. If someone types a negative salary, enters a date in the wrong format, or leaves a required field blank — your reports, queries, and calculations all break down.
MS Access gives you tools to prevent this at the table level, before any bad data ever gets saved. You don’t need code. You just need to know where to look.
Let’s go through all 14.
1. Field Captions
By default, Access uses your field name as the column header — so CustID shows up as CustID. A caption lets you display a friendlier label like Customer ID without renaming the field itself.
This is a small thing, but it makes your database look polished and professional, especially when building forms or reports later.
2. Currency Format
When you set a field’s data type to Currency, Access doesn’t automatically decide how it looks on screen. You control that through the Format property.
You can choose from built-in options like Currency (which adds a currency symbol and two decimal places) or Standard, Fixed, and others. Pick the one that fits how your data will be read and reported.
3. Calculated Fields with the Expression Builder
You saw a basic version of this in Level-1. In Level-2, it goes a bit further. The Expression Builder inside table Design View lets you create a field whose value is automatically computed from other fields in the same record.
For example, a TotalPrice field can multiply Quantity by UnitPrice automatically. Access handles the math — you just define the formula once.
4. Capitalizing Text
If you want names or labels to always appear in a specific case, you can use a format code to enforce it. Setting the Format property of a text field to > converts everything entered into uppercase. Using < forces lowercase.
This is a clean way to keep data consistent without relying on users to type correctly.
5. Input Mask for Passwords
An Input Mask controls how data is entered into a field — it defines the pattern of characters allowed. For a password field, you can use the Password input mask, which displays asterisks as the user types, hiding the actual characters.
Input masks are also useful for phone numbers, postal codes, and any field where the format is fixed and predictable.
6. Preventing Zero-Length Strings
A zero-length string is when someone presses the spacebar and moves on — technically there’s something in the field, but it’s not useful data. Access treats it differently from a truly empty (null) field, which can cause confusion in queries.
Setting the Allow Zero Length property to No prevents this. Combined with Required = Yes, you can make sure a field is always properly filled in.
7. Validation Rules
This is one of the most powerful features in table design. A Validation Rule is a condition the data must meet before it gets saved.
For example:
- A field storing age must be
>= 18 - A discount field must be
<= 100 - A status field must be one of a specific list of values
You write the rule in the Validation Rule property, and you write the error message that appears when the rule is broken in the Validation Text property. Access enforces it automatically every time a record is saved.
8. Recalling Previously Entered Data
This one is a time-saver. In Access, you can set a field’s Default Value so that it automatically carries forward the last value entered. This is useful for fields where data repeats often — like a department name, a city, or a status code.
Instead of retyping the same value on every new record, the field pre-fills it for you. You only change it when needed.
9. Restricting Numeric Data
Similar to validation rules, you can restrict a numeric field to only accept values within a certain range. For instance, a Rating field might only allow values between 1 and 5. Any entry outside that range gets rejected with your custom error message.
This is done through the same Validation Rule property — just written as Between 1 And 5 or >= 1 And <= 5.
10. Predefined Format Patterns
Beyond the simple uppercase/lowercase trick, Access supports custom format strings for text and number fields. You can define exactly how data displays — even if it was entered differently.
For example, a phone number field might store 03001234567 but display it as 0300-123-4567. The raw data stays the same; the format is just for display. This gives you control over presentation without changing the underlying values.
11. Advanced Calculated Fields
Building on technique #3, the advanced version of calculated fields involves more complex expressions — ones that use built-in functions like IIf() (Access’s version of IF), DateDiff(), or nested calculations.
For example, you could create a field that automatically flags a record as “Overdue” if a due date has passed, or calculate someone’s age from their date of birth. The Expression Builder helps you construct these without memorizing every function.
12. Preventing Null Values in Numeric Fields
A null value means no data was entered at all — and in numeric fields, that can cause real problems. If you try to add or average a column that has nulls, the result can be wrong or missing entirely.
Setting Required = Yes on a numeric field forces the user to enter a value. You can also combine this with a Default Value (like 0) so the field always has something in it, even if the user doesn’t type anything.
13. Date Validation Rules
Dates need their own validation because they have more ways to go wrong. You might want to make sure a date is in the past (like a birth date) or in the future (like a deadline).
For example, setting a validation rule of <= Date() on a DateOfBirth field means Access rejects any birth date that hasn’t happened yet. The Date() function returns today’s date automatically, so this rule stays accurate without any manual updates.
14. Validation Between Two Dates
The final technique is about comparing two date fields against each other. For instance, a StartDate should always be before an EndDate. If someone enters an end date earlier than the start date, the record shouldn’t save.
You write this as a Table-Level Validation Rule (not just a field-level one), which checks both fields together. In Access, this lives under the table’s property sheet and uses syntax like [EndDate] >= [StartDate].
This kind of cross-field validation is something Excel simply can’t do — and it’s a great example of why Access is worth learning.
All 14 at a Glance
| # | Technique | What It Does |
|---|---|---|
| 1 | Captions | Friendly display names for fields |
| 2 | Currency Format | Controls how money values appear |
| 3 | Calculated Fields | Auto-compute values using expressions |
| 4 | Capitalize Text | Force uppercase or lowercase input |
| 5 | Input Mask (Password) | Hide characters as they’re typed |
| 6 | No Zero-Length Strings | Block empty-looking but non-null entries |
| 7 | Validation Rules | Set conditions data must meet |
| 8 | Recall Previous Data | Pre-fill fields with the last entered value |
| 9 | Restrict Numeric Range | Allow only values within a set range |
| 10 | Format Patterns | Control how data displays on screen |
| 11 | Advanced Calculated Fields | Complex expressions using built-in functions |
| 12 | Prevent Nulls in Numbers | Make numeric fields required |
| 13 | Date Validation | Ensure dates are in a valid range |
| 14 | Validation Between 2 Dates | Cross-field date logic at the table level |
Final Thoughts
Most people who use MS Access only scratch the surface of what tables can do. They create fields, enter data, and move on. But these 14 techniques give you real control — over what gets entered, how it looks, and what gets rejected before it causes problems downstream.
The best part is that all of this happens at the table level. Which means every form, query, and report built on top of your table automatically benefits from these rules. You set them once, and they work everywhere.
If you haven’t watched Level-1 yet, start there — it covers the fundamentals of tables, queries, and relationships. Then come back to this and you’ll have a much stronger foundation.
Watch the full MS Access Tutorial Level-2 on YouTube by Skill Header, and download the practice files at skillheader.com.
