to restrict entries based on a formula what validation criteria should you use

You can vet or validate information in Access desktop databases as you enter it past using validation rules. You can use the expression architect to help you format the rule correctly. Validation rules can exist set in either tabular array blueprint or table datasheet view. There are iii types of validation rules in Access:

1. Field Validation Rule Y'all can use a field validation rule to specify a criterion that all valid field values must meet. You should not have to specify the current field every bit a part of the rule unless you are using the field in a office. 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 take a validation rule that disallows values in the past.

Quick examples:

Disallow date values in the by: >=Date()

By and large accepted email format: Is Null OR ((Like "*?@?*.?*") AND (Non Similar "*[ ,;]*"))

Number less than or qual to 5: <=5

Currency field can't be negative: >=0

Restrict character length in cord: Len([StringFieldName])<100

Field validation rule displayed in Access table designer

ii. Record Validation Dominion Yous can use a tape validation rule to specify a condition that all valid records must satisfy. Yous can compare values beyond different fields using a record validation rule. For example, a record with two date fields might require that values of i field ever precede values of the other field (due east.g., StartDate is before EndDate).

Quick examples:

Ensure the end date doesn't come up earlier the start engagement: [Terminate Date]>=[Start Date]

Enter a required appointment that occurs no more than than thirty days after the order date: [RequiredDate]<=[OrderDate]+30

A table valdation rule in the Access table designer.

3. Validation on a class You lot tin can use the Validation Rule property of a control on a form to specify a criterion that all values input to that control must see. The Validation Rule control property works like a field validation rule. Typically, you lot use a form validation rule instead of a field validation rule if the rule was specific only to that form and not to the tabular array no matter where information technology was used.

Overview

This commodity explains how to use validation rules and validation text in tabular array fields and form controls. A validation rule is one way to restrict input in a table field or a control (such every bit a text box) on a form. Validation text lets yous provide a message to assistance users who input data that is not valid.

When data is entered, Access checks to see whether the input breaks a validation rule – if so, the input is not accepted, and Access displays a message.

Admission provides a number of ways to restrict input:

Data types Every table field has a information type that restricts what users can enter. For example, a Date/Time field accepts only dates and times, a Currency field accepts simply monetary data, and so on.
Field properties Some field backdrop restrict data input. For example, the Field Size property of a field restricts input by limiting the corporeality of data.

You can also employ the Validation Rule property to require specific values, and the Validation Text property to alert your users to any mistakes. For case, entering a rule such every bit >100 And <1000 in the Validation Rule property forces users to enter values between 100 and 1,000. A rule such as [EndDate]>=[StartDate] forces users to enter an ending engagement that occurs on or after a starting engagement. Entering text such as "Enter values between 100 and i,000" or "Enter an ending date on or after the showtime date" in the Validation Text property tells users when they accept fabricated a fault and how to fix the error.
Input masks Y'all tin use an input mask to validate data by forcing users to enter values in a specific way. For example, an input mask can force users to enter dates in a European format, such as 2007.04.xiv.

You can employ these methods of validating information alone or in combination with each other. Data types are not optional, and provide the most basic type of data validation.

For more data most data types, field sizes, and input masks, see the article Introduction to data types and field backdrop.

Types of validation rules

Yous tin create ii basic types of validation rules:

Field validation rules Use a field validation rule to check the value that you enter in a field when you leave the field. For example, suppose you have a Date field, and you lot enter >=#01/01/2010# in the Validation Rule property of that field. Your rule at present requires users to enter dates on or subsequently January 1, 2010. If yous enter a appointment before than 2010 and then effort to place the focus on another field, Access prevents you from leaving the electric current field until you lot fix the trouble.
Record validation rules Utilise a tape validation rule to command when you can save a record (a row in a table). Unlike a field validation rule, a record validation rule refers to other fields in the aforementioned tabular array. You create tape validation rules when you demand to cheque the values in one field against the values in another. For example, suppose your concern requires you to ship products within 30 days and, if y'all don't ship within that time, you must refund part of the purchase price to your customer. You can define a record validation rule such as [RequiredDate]<=[OrderDate]+30 to ensure that someone doesn't enter a send date (the value in the RequiredDate field) also far into the future.

If the syntax for validation rules looks cryptic, see the section What you can put in a validation rule for an explanation of the syntax and some case validation rules.

Where you lot tin can use validation rules

Yous can define validation rules for table fields and for controls on forms. When you define rules for tables, those rules employ when you import data. To add validation rules to a table, y'all open the table and use commands on the Fields tab of the ribbon. To add validation rules to a form, y'all open the form in Layout view and add rules to the properties of individual controls.

The steps in the section Add a validation rule to a tabular array explain how to add validation rules to table fields. The steps in the department Add a validation rule to a command on a grade, later in this article, explain how to add rules to the backdrop in individual controls.

What you tin put in a validation rule

Your validation rules tin can contain expressions — functions that return a single value. Yous can employ an expression to perform calculations, manipulate characters, or exam information. A validation rule expression tests data. For example, an expression can check for one of a series of values, such equally "Tokyo" Or "Moscow" Or "Paris" Or "Helsinki". Expressions can also perform mathematical operations. For instance, the expression <100 forces users to enter values that are less than 100. The expression ([OrderDate] - [ShipDate]) calculates the number of days that elapsed between the time an society was placed and the time information technology shipped.

For more information well-nigh expressions, run across the commodity Build an expression.

Superlative of Folio

Add a validation rule to a tabular array

Y'all can add a field validation dominion and/or a tape validation rule. A field validation dominion checks input to a field, and is applied when the focus leaves the field. A record validation rule checks input to one or more fields is applied when the focus leaves the record. Commonly, a tape validation rule compares the values of 2 or more than fields.

Notes:The following field types do not back up validation rules:

AutoNumber
OLE Object
Attachment
ReplicationID

Create a field validation dominion

Select the field that y'all desire to validate.
On the Fields tab, in the Field Validation grouping, click Validation, so click Field Validation Rule.
Use the Expression Builder to create the rule. For more than information near using the Expression Builder, run across the commodity Use the Expression Builder.

Create a bulletin to display for field input that is not valid

Select the field that needs a message for input that is not valid. The field should already have a validation rule.
On the Fields tab, in the Field Validation group, click Validation, so click Field Validation Message.
Enter an advisable bulletin. For example, if the validation rule is >10, the message might be "Enter a value that is less than 10."

For some examples of field validation rules and letters, run into the section Validation dominion reference.

Create a record validation rule

Open the tabular array for which you want to validate records.
On the Fields tab, in the Field Validation group, click Validation, so click Record Validation Rule.
Employ the Expression Builder to create the rule. For more information about using the Expression Builder, come across the article Utilise the Expression Builder.

Create a bulletin to display for record input that is not valid

Open the table that needs a message for input that is not valid. The tabular array should already have a record validation dominion.
On the Fields tab, in the Field Validation grouping, click Validation, and then click Record Validation Message.
Enter an appropriate bulletin. For example, if the validation dominion is [StartDate]<[EndDate] , the message might be "StartDate must precede EndDate."

Top of Folio

Test existing information confronting a new validation dominion

If you add together a validation rule to an existing tabular array, you might want to examination the rule to see whether any existing data is not valid.

Open the tabular array that you want to exam in Blueprint View.

On the Design tab, in the Tools group, click Test Validation Rules.
Click Yes to close the alert bulletin and start the exam.
If prompted to save your table, Click Yep.
You might see a variety of other alert messages as you lot proceed. Read the instructions in each message, and then click Yes or No, as appropriate, to complete or terminate the testing.

Top of Page

Add together a validation rule to a command on a form

You tin utilise the Validation Rule property and the Validation Text holding of a form control to validate data that is input to that control and to help users who input data that is non valid.

Tip:If you create a form automatically from a tabular array by using one of the Form commands on the ribbon, whatsoever validation for fields in the underlying table are inherited by the corresponding controls on the form.

A control can accept a different validation rule from the table field to which the control is jump. This is useful if you desire the grade to be more restrictive than the tabular array. The form rule is applied, and then the table rule is applied. If the table is more restrictive than the form, the rule defined for the table field takes precedence. If the rules are mutually sectional, they prevent y'all from entering whatever data at all.

For instance, suppose yous apply the following rule to a engagement field in a table:

<#01/01/2010#

Merely you then apply this rule to the form command that is bound to the engagement field:

>=#01/01/2010#

The engagement field now requires values earlier than the year 2010, but the course control requires dates accept that yr or afterward, thus preventing you lot from inbound whatever data at all.

Create a validation rule for a control

Right-click the form that you want to change, and and then click Layout View.
Right-click the control that you want to modify, and then click Properties to open the belongings sheet for the control.
Click the All tab, and then enter your validation rule in the Validation Rule holding box.

Tip:Click the Build button to start the Expression Builder.

For more information about using the Expression Builder, run across the article Use the Expression Architect.
Enter a message in the Validation Text property box.

Top of Page

Validation rule reference

Validation rules employ Admission expression syntax. For more information nigh expressions, see the commodity Introduction to expressions.

Validation rule and validation text examples

Validation rule	Validation text
<>0	Enter a nonzero value.
>=0	Value must be nothing or greater. -or- You must enter a positive number.
0 or >100	Value must be either 0 or greater than 100.
BETWEEN 0 AND 1	Enter a value with a percentage sign. (For employ with a field that stores number values every bit 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]), [LastName],0) = 0	Data in a field named LastName must be uppercase.
>=Int(Now())	Enter today'due south date.
M Or F	Enter M for male or F for female person.
Similar "[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.
[RequiredDate]<=[OrderDate]+xxx	Enter a required date that occurs no more than xxx days subsequently the order appointment.
[EndDate]>=[StartDate]	Enter an ending appointment on or after the first date.

Syntax examples for common validation rule operators

Operator	Function	Example
Non	Tests for converse values. Use earlier any comparing operator except IS NOT Cipher.	NOT > 10 (the same every bit <=x).
IN	Tests for values equal to existing members in a list. Comparison value must be a comma-separated listing enclosed in parentheses.	IN ("Tokyo","Paris","Moscow")
Betwixt	Tests for a range of values. You must employ two comparison values — low and high — and you must carve up those values with the AND separator.	BETWEEN 100 AND yard (the same as >=100 AND <=thou)
LIKE	Matches pattern strings in Text and Memo fields.	*LIKE "Geo"**
IS Not Cipher	Forces users to enter values in the field. This is the same as setting the Required field belongings to Aye. Yet, when you enable the Required belongings and a user fails to enter a value, Access displays a somewhat unfriendly fault bulletin. Typically, your database is easier to utilise if you use IS NOT NULL and enter a friendly bulletin in the Validation Text property.	IS NOT Null
AND	Specifies that all parts of the validation rule must be truthful.	>= #01/01/2007# AND <=#03/06/2008# Notation:Yous can also employ AND to combine validation rules. For example: *NOT "UK" AND Like "U"**.
OR	Specifies that some but non all parts of the validation rule must be truthful.	January OR February
<	Less than.
<=	Less than or equal to.
>	Greater than.
>=	Greater than or equal to.
=	Equal to.
<>	Non equal to.

Using wildcard characters in validation rules

You lot can apply wildcard characters in your validation rules. Keep in mind that Access supports two sets of wildcard characters: ANSI-89 and ANSI-92. Each of those standards uses a dissimilar prepare of wildcard characters.

By default, all .accdb and .mdb files employ the ANSI-89 standard.

You can alter the ANSI standard for a database to ANSI-92 by using the following process:

On the File tab, click Options.
In the Access Options dialog box, click Object Designers.
In the Query design department, under SQL Server Uniform Syntax (ANSI-92), select This database.

For more than information virtually using wildcard characters and the ANSI standards for SQL, see the article Admission wildcard graphic symbol reference.

Summit of Page

swanntiondes1946.blogspot.com

Source: https://support.microsoft.com/en-us/office/restrict-data-input-by-using-validation-rules-b91c6b15-bcd3-42c1-90bf-e3a0272e988d