Weekly Computer Tip # 56
6 March 2004

Using validation rules to ensure accurate data entry (Microsoft Access)

Consistent data entry is essential for maintaining a healthy database. In tip # 7 I explained how to automatically enter the current date in a table by entering =Date() in the Default Value Field Properties section of the table Design view. However,

Did you know ...

Default values can be overwritten, so to ensure truly accurate data entry (we're only human!) you might want to use validation rules. (For example, you can prevent a user from entering a date that occurred in the past.) When data is entered that violates the ValidationRule setting you can use the ValidationText property to specify the message to be displayed to the user.

Here's how:

1. Open the table in Design view
2. In the upper portion of the window, click the relevant date field
3. In the lower portion of the window on the General tab, click the ValidationRule property box
4. Type >Date()
5. In the ValidationText property box, type the message that you want displayed when the rule is broken. (For example "Entry must be a date in the future")

If you set a validation rule in a field that contains data, Microsoft Access will ask if you want to apply the new rule to existing data when you save the table.

Until next week.

PS I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.

Home   Training   Support   Clients   Contact Details   News   Disclaimer