Business Solutions: Data Validation-Access 2000 

     By James D. Duncan, CPA    May 2004

            The single largest problem I see is data that is not consistent.  Be this social security numbers, telephone numbers or a variety of items that should be standard from one record to the next.  I am continually amazed at the commercial software that does not enforce formatting of the input fields.  I am currently working with 2 pharmacy systems that have free form social security number fields.  And the customer is always surprised when I have come to add a report and have to tell them we need to spend a week on cleaning data before the new report will be usable.

            The best time to set the input format is in the table design.  The input mask property will set the format that the field will be input into.  There are masks that come with Access and you can roll your own.  Look at mask in the help and that will give you much more explanation.  Then click on the input mask field on the table you are in and press F1 and get the specifics for input masks.

            In the example above I use input masks even for name fields.  Notice that the input mask is in 3 sections separated by semi colons.  First is the mask description, second is storage method (I always use 1) and then the place holder.  Notice in the example above I put in 20 question mark’s in the beginning and an underline at the end.  When this field is input on a new record you will get 20 underlines that notify the user of the maximum field length. As shown below.  As soon as I input the first letter the underline appears and I know how long the field is and where I am in inputting the total letters allowed.

            The nice thing about putting the input mask in the table is when you build the form based on the table the input mask comes with it and you do not have to do anything else.  But usually you have already built the table before you knew about input masks or you have inherited the lovely thing.  Rather like adopted dogs, but that is another story.

            So, we can modify the input mask in the form.  Open the form in design view, and put your input mask there. 

            Let us look at the standard Input Mask for a moment.  Click on the input mask property and you will the 3 ellipses to the right of the input area.  Click on the drop down and input mask wizard appears to help us.  It is straight forward but does not include what I have just shown you because it has no way of knowing the length of the field you are working with.  The help will allow you to expand on this.

             Pick what you need for the appropriate field.  Then try it here and on the form.  You will be surprised many times because you got EXACTLY what you asked for.  I just hate it when that happens.

            Like when you try to have a name field with the first letter capitalized and some day you try to put in McDonald and it will not let you capitalize the “d”.  Sorry, you said only 1 capital letter.  Capitalization is not standard in the English language so why would we think we can set a standard for it in the program.

All of this does nothing for the existing data in the file.  There are many books written on data laundering and I think I must own half of them.  With that said, I have only covered about 10% of them.  Thanks for the e-mails.  These are the only way I know I am not writing this column just for my own entertainment.  {;>)

James is a member of the OKCPCUG and an expert in Access and Excel. Click here to reach James.