Sometimes, you may decide that you want to add or modify fields in your tables.
Access makes it easy to do this, although there are some important things that you'll
need to consider.
There are three different rules that you can apply to control the type of data that the
fields can accept.
And these are data types, character limits, and validation rules.
To get started, make sure that you have a table open.
I'm using the Customers table.
We're going to scroll all the way to the right until you see the blank field that says Click
to Add.
And when you click on it, you'll see a list of all of the data types that you can choose
from.
Text is the default option, and this is the type that you'll use for regular text, such
as a person's name or address.
You'll also use it for numbers that aren't used in calculations, like a phone number
or zip code.
For any numbers that you might want to do calculations with, you'll need to choose Number.
For example, we would use this for the quantities of an item that were sold.
If you're dealing with money, then you'll choose Currency.
And if you choose Date & Time, then when you're editing that field, you'll see a calendar
icon, which you can click to select a date.
But in this case, I just want a simple Yes or No answer, so I'll choose this one.
And this adds a checkbox for each record.
Then you can type in the name of your field.
I'm going to name it "Add to Mailing List?"
Then I'll resize this field.
Now we can just check each customer who wants to be on our mailing list.
And you can also click and drag the name to move the field wherever you like.
If you decide that you need a different data type for a field, then you can just select
it, and then go to the Fields tab… and click the Data Type drop-down arrow.
I'm going to change this to text so I can add additional information in this field.
Some of our customers want to receive our weekly newsletter, but others just want to
hear about our special events.
Now you should be very careful about changing the data type because it is possible to lose
some of your data if you change it to the wrong type.
In some fields, you may want to narrow down the data even further by adding a character
limit.
And you can only do this with text fields.
For example, we want all of the states to be formatted the same way, so we're only using
the two-letter state abbreviations.
If some of the records say NC, and others say North Carolina, then Access won't know
to group them together—which means our sorts, filters, and queries may not work right.
In the Fields tab, find the field size box.
The default field size is 255 characters, but I'm going to change it to 2.
And you may get a warning message here, so if you're sure you want to change the field
size, click Yes.
In this case, setting a character limit isn't quite good enough because somebody could still
type a two-letter code that's not a real state (for example NX).
To prevent that, we need to be a lot more specific about what types of input this field
will accept.
And we'll do that by adding a validation rule.
In the Fields tab, go to the Validation command on the far right… and select Field Validation
Rule.
Here, I'm going to type each state abbreviation in quotation marks, and I'm separating each
one with the word "Or."
And this may take a while.
So this expression is just looking for exact matches, but if you want, you can create validation
rules using the same syntaxes that we talked about in the query lessons.
For example, you could use the Like syntax to set a validation rule that only allows
text that ends with "cake."
Or, if you're using numerical values, you could use greater than to make sure that none
of our quantities are negative.
When you're done, click OK.
Whenever you create a validation rule, it's important to create a validation message.
And this option is also under the Validation command.
This message will pop up whenever somebody tries to type in something that doesn't follow
the validation rule.
The message should briefly describe the rule, so that the user can then make the necessary
corrections.
In this case, I'll just mention that it needs to be a 2-letter abbreviation.
Now you won't need to add a character limit or validation rule to all of your fields,
but you can add one or both of them whenever you want to control the types of information
that your field will accept.