Goodwill Community Foundation creating
opportunities for a better life
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 a 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 short
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 we're sold if you're
dealing with money then you'll choose
currency and if you choose date and 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 in c 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
two 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 in X 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 syntax as 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 two 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