Data Validation
in ms access
COMPUTING AND ICT
SOPH BSE & BBI
Data validation
• Restrict data input by using validation rules
• You can vet or validate data in Access desktop databases as you enter it by using
validation rules.
• Validation rules can be set in either table design or table datasheet view. There are
three types of validation rules in Access:
• Data Types: Setting appropriate data types (e.g., Text, Number, Date/Time) for each
field in your table is the first layer of validation.
• Field Size: Set the maximum number of characters for text fields or specify the
numeric range for number fields.
• Format: Use predefined formats for dates, numbers, etc., to ensure consistent data
entry.
• Default Value: Provide a default value to reduce user input errors or standardize
responses.
• Required: Set fields to “Required” to ensure that a value must be entered before
saving a record.
• 1. Field Validation Rule You can use a field validation rule to specify a
criterion that all valid field values must meet.
• Restrictions on types of characters to be entered in a field may be easier to
do with an Input Mask.
• For example, a date field might have a validation rule that disallows values in
the past.
• Quick examples:
• Disallow date values in the past: >=Date()
• Generally accepted email format: Is Null OR ((Like "*?@?*.?*") AND (Not Like
"*[ ,;]*"))
• Number less than or qual to five: <=5
• Currency field can't be negative: >=0
• Restrict character length in string: Len([StringFieldName])<100
example
• 2.Record Validation Rule You can use a record validation rule
to specify a condition that all valid records must satisfy. You can
compare values across different fields using a record validation
rule. For example, a record with two date fields might require that
values of one field always precede values of the other field (e.g.,
StartDate is before EndDate).
• Quick examples:
• Ensure
the end date doesn't come before the start date: [End
Date]>=[Start Date]
• Entera required date that occurs no more than 30 days after the
order date: [RequiredDate]<=[OrderDate]+30
Continue---
• 3.Input MasksInput Masks: Use input masks to enforce a specific
format for data entry.
• This
is helpful for fields like phone numbers, zip codes, and Social
Security numbers.Example: A phone number might have the input
mask \(999") "000\-0000 for (123) 456-7890.
example
Continue--------
• 4.
Lookup FieldsUse lookup fields to create dropdown lists in your
tables.
• This restricts users to a list of valid choices.
• Thishelps with maintaining consistent data entry, especially for
fields like country, state, or department.
• 3.Validation on a form You can use the Validation Rule property of a
control on a form to specify a criterion that all values input to that control must
meet. The Validation Rule control property works like a field validation rule.
Typically, you use a form validation rule instead of a field validation rule if the
rule was specific only to that form and not to the table no matter where it was
used.
AND Specifies that all parts of the validation rule must be true. >= #01/01/2007# AND
<=#03/06/2008#
Note: You can also use AND to
combine validation rules. For
example: NOT "UK" AND LIKE
"U*".
OR Specifies that some but not all parts of the validation rule must be true. January OR February
< Less than.
<= Less than or equal to.
> Greater than.
>= Greater than or equal to.
= Equal to.
<> Not equal to.
Continue-----
• 2.Validation Rules and TextValidation Rule: Define rules for each
field or the entire record.
• Access checks these rules when data is entered or modified
• Example: >= 18 for an Age field to ensure users are at least 18
years old.
• ValidationText: Customize the error message displayed when the
Validation Rule is violated, helping users understand what’s wrong
with their entry.
Validation rule and validation text examples
Validation rule Validation text
<>0 Enter a nonzero value.
Value must be zero or greater.
>=0 -or-
You must enter a positive number.
0 or >100 Value must be either 0 or greater than 100.
Enter a value with a percent sign. (For use with a field that stores number
BETWEEN 0 AND 1
values as percentages).
<#01/01/2007# Enter a date before 2007.
>=#01/01/2007# AND <#01/01/2008# Date must occur in 2007.
<Date() Birth date cannot be in the future.
StrComp(UCase([LastName]),
Data in a field named LastName must be uppercase.
[LastName],0) = 0
>=Int(Now()) Enter today's date.
M Or F Enter M for male or F for female.
LIKE "[A-Z]*@[A-Z].com" OR "[A-Z]*@[A-Z].net" OR "[A-Z]*@[A-Z].org" Enter a valid .com, .net, or .org e-mail address.
Enter a required date that occurs no more than 30 days after the order
[RequiredDate]<=[OrderDate]+30
date.
[EndDate]>=[StartDate] Enter an ending date on or after the start date.
Only character accept
Only numbers accepted
Syntax examples for common validation rule operators
Operator Function Example
NOT Tests for converse values. Use before any comparison operator except IS NOT NULL. NOT > 10 (the same as <=10).
IN Tests for values equal to existing members in a list. Comparison value must be a comma-separated list enclosed IN ("Tokyo","Paris","Moscow")
in parentheses.
BETWEEN Tests for a range of values. You must use two comparison values — low and high — and you must separate BETWEEN 100 AND 1000 (the same as >=100 AND
those values with the AND separator. <=1000)
LIKE Matches pattern strings in Text and Memo fields. LIKE "Geo*"
IS NOT NULL Forces users to enter values in the field. This is the same as setting the Required field property to Yes. However, IS NOT NULL
when you enable the Required property and a user fails to enter a value, Access displays a somewhat unfriendly
error message. Typically, your database is easier to use if you use IS NOT NULL and enter a friendly message in
the Validation Text property.
Referential IntegrityEnforce referential integrity
• 6.Referential IntegrityEnforce referential integrity between related
tables in relationships to ensure data consistency.For instance,
ensuring a foreign key in a child table has a matching primary key
in a parent table prevents orphaned records.
