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, you 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 that 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.
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.