To learn more about Access Training click here
We’ve all heard the expression garbage in – garbage out. Bad inputs produce bad outputs. One feature of databases is the ability to validate data. Data validation restricts what comes in via data entry (typing, not importing). Although i would prefer that you typically validate at the form level instead of the table level (since form level validation is less restrictive), you should nonetheless understand table level (global) data validation.
To download a sample database click here. The database tracks employees by department and will allow you to determine payroll costs by department.
Open tblEmployees by double- clicking the tblEmployees table in the navigation pane on the left. Tap the tab key to navigate to the right across column (fields) and note that for each employee we track hours worked and hourly rate. I want to restrict the data entry in the hourly rate field to numbers between $8.00 to $50.00. Note that i stress data entry because if you import data (from excel, a text file, or another database) and then run an append query you’ll be warned about breaking data validation rules, which you’ll have to ignore to complete the append query.
Switch the table from datasheet to design view
and click anywhere in to hourly rate field row.
At the bottom of design view click in the validation rule row. Click the … to display the Expression builder.
In the expression builder, type the following expression
>=8 and <=50
The expression reads allow values greater than or equal to 8 and less than or equal to 50.
Switch the table from design view to datasheet view and when prompted, choose yes to save your changes to the table’s design (and click yes twice to ignore the warnings about validation rules – in general, do not ignore warnings, just in the context of this tutorial!).
Navigate right to the hourly rate column. Change the value from 8.50 To 55 (an invalid value). To complete the edit press either the tab or enter key and note the generic access error message.
Press the escape key to cancel the invalid edit, then type 12. When you press the enter or tab key access allows the value.
You can customize the error message so the user understands the requirements for a valid value. Switch the table back to design view and click on the hourly rate field. On the properties panel, click in the Validation Text box and type a validation message like
Please enter a number between 8 and 50
To test the new message, switch from design view to datasheet view (click yes to save changes) and enter an invalid value in the hourly rate column, then press the tab or enter key to view the custom error message.
As i mentioned earlier, a less restrictive type of validation can occur at the form level. Open the form named frmValidate (double click the object in the navigation pane. Note the hours worked field is not restricted. Type a value like 200 in the hours worked text box and press the tab key. It’d be nice to get paid for a 200 hour work week but there are only 168 hours in a week, 42 of which i need to sleep! I want to restrict users to entering whole numbers (integers with no decimals) between 0 and 126. Switch the frmValidate to design view. Show the properties of the Hours Worked textbox by right clicking along the border of the box, and then left clicking on the properties command. In the properties pane one the right, select the data tab and click in the validation rule box and left click the build command to access the expression builder.
Type an expression like
>=0 and <=126
And click the ok button to return to form design. Switch from design view to form view and try entering an invalid value like 222 (press the tab or enter key to activate the validation settings and note the generic error message.
To customize the error message, in form design view select the text box control and on the properties panel type a message in the validation text box.
If we wanted to further restrict to values that included .25, .5, or .75 (15/30/45 minutes) then we could either write a complex expression, a macro or vba procedure to handle that aspect of the validation.
Readers are encouraged to post comments about this post. Feel free to make specific requests for additional assistance.