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
datatype 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 okay 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