Introduction

After working through your database, you can find that you need to make some transforms to the tables that store your data. Access renders it simple to modify your tables to suit your database's requirements.

You are watching: How to change field size in access

In this leskid, you'll learn exactly how to create and also rearrange table fields. You'll likewise learn just how to ensure your table information is properly and continuously formatted by establishing validation rules, character limits, and also information types in your fields. Finally, we will certainly straight you to added choices for perdeveloping straightforward math attributes within your tables.


Throughout this tutorial, we will be using a sample database. If you would certainly like to follow along, you'll have to download our Access 2013 sample database. You will should have actually Access 2013 set up on your computer system in order to open the instance.

Modifying tables

In enhancement to making fundamental changes to your tables, prefer adding and moving areas, you can make more advanced adjustments that let you set rules for your data. All of these transforms deserve to aid make your tables also even more valuable.

Adding and rearvarying fields

Access makes it basic to rearvariety existing areas and also add new ones. When you include a new area, you deserve to even set the data type, which dictates which type of information can be entered right into that field.

Tbelow are numerous kinds of areas you deserve to include to a table:

Quick Text: This is the default choice and is ideal for many message in Access. You should additionally select it for numbers you don't arrangement to perform math with, choose postal codes and also phone numbers.Number: This is finest for numbers you can want to execute calculations via, choose amounts of a things ordered or offered.Currency: This immediately formats numbers in the currency supplied in your region.Date & Time: This allows you to choose a date from a pop-out calendar.Yes/No: This inserts a checkbox right into your area.Rich Text: This allows you to add formatting to message, like bold and also italics.Long Text: This is appropriate for big quantities of message, favor product descriptions.Attachment: This enables you to attach papers, favor imperiods.Hyperlink: This creates a connect to a URL or email resolve.To add a new area to an existing table:Open the preferred table, then click the header with the message Click to Add. If you currently have actually a number of fields, you may should scroll all the method to the right to see this choice.
*
Adding a brand-new field
*
Selecting a file type
*
Naming the brand-new field
To relocate a field:Locate the area you want to relocate, then float your computer mouse over the bottom border of the area header. The cursor will come to be a four-sided arrow.
*
Hovering the computer mouse over the field
*
Moving a field

Advanced area options

On the previous web page, you learned around establishing the data type for brand-new fields. When you set field data kind, you are really setting a rule for that field. Databases regularly encompass rules because they assist ensure individuals enter the correct type of information.

Why is this important? Computers aren't as smart as humans about particular things. While you might recognize that two and also 2 or NC and North Carolina are the exact same thing, Access will certainly not and therefore won't group these things together. Making certain to enter your data in a typical format will certainly aid you better organize, count, and also understand it.

Rules deserve to also identify which options you have for functioning through your information. For instance, you can just execute math through data gotten in in number or currency fields, and you deserve to just format message entered into text areas.

Tbelow are 3 major types of rules you can collection for a field: data type, character limit, and also validation rules.

To change the data type for existing fields:Select the field whose data type you desire to adjust.Select the Fields tab, then locate the Formatting team. Click the Data Type drop-down arrow.
*
Clicking the File Type drop-dvery own arrow
Select the preferred data type.
*
Selecting a new area information type
The area data kind will certainly be readjusted. Depfinishing on the data kind you determined, you might alert alters to your information. For circumstances, bereason we collection the data type for the Email area to Hyperlink every one of the email addresses in the field are currently clickable links.

You shouldn't change area data type unless you are particular your area information is in the correct format for the brand-new data form. Changing a field containing only text to the Number type, for instance, will certainly delete every one of your field data. This process is often irreversible.

Field character limits

Setting the character limit for a area sets a dominion about just how many characters—letters, numbers, punctuation, and even spaces—deserve to be entered into that field. This deserve to help to keep the data in your records concise and also also force users to enter data a certain means.

In the example below, a user is entering records that incorporate addresses. If you collection the character limit in the State area to 2, users have the right to just enter 2 characters of information. This implies they should enter postal abbreviations for the claims rather of the complete name—below, NC instead of North Carolina. Keep in mind that you can just set a character limit for areas characterized as text.

To collection a character limit for a field:Select the preferred field.Click the Fields tab, then find the Properties team.In the Field Size box, kind the maximum number of personalities you want to enable in your field.
Save your table. The character limit for the field will be set.Validation rules

A validation rule is a ascendancy that dictates which indevelopment can be gotten in right into a area. When a validation dominion is in location, it is impossible for a user to enter information that violates the dominion. For example, if we were asking individuals to input a state name right into a table with call information, we might create a rule that limits the valid responses to UNITED STATE state postal codes. This would certainly proccasion individuals from inputting something that wasn't actually a real state postal code.

In the example below, we will certainly apply that dominance to our Customers table. It's a relatively basic validation rule—we'll just name every one of the valid responses a user could enter, which will intend users can't type anypoint else right into the document. However, it's feasible to produce validation rules that are much even more complex. For detailed information on exactly how to compose validation rules, review this tutorial from Microsoft on creating validation rules.

To produce a validation rule:Select the field you desire to add a validation dominance to. In our example, we'll set a dominance for the State field.Select the Fields tab, then find the Field Validation group. Click the Validation drop-down command, then select Field Validation Rule.
The Expression Builder dialog box will show up. Click the message box and also type your validation preeminence. In our instance, we desire to limit information in the State area to actual state postal codes. We'll kind each of the valid responses in quotation marks and sepaprice them via the word Or, which allows Access know that this area can accept the response "AL" Or "AK" Or "AZ" or any kind of of the various other terms we've gotten in.
*
Typing our validation ascendancy, which will contain eexceptionally state postal code, each separated by the word "Or"
Once you're satisfied with the validation ascendancy, click OK. The dialog box will cshed.Click the Validation drop-dvery own command also aacquire. This time, pick Field Validation Message.
A dialog box will appear. Type the expression you want to show up in an error message when users attempt to enter information that violates the validation dominance. Your message need to let them know what information is permitted.
When you're satisfied with the error message, click OK.The validation rule is now had in the field. Users will certainly be unable to enter data that violates the preeminence.

Simple validation rules can be created exactly choose query criteria. The only distinction is that query criteria search for information, while an identical validation preeminence either permits or rejects data. To check out examples of query criteria, evaluation our Query Criteria Rapid Reference Guide.

More table options

Calculated fields and totals rows

Adding calculated fields and totals rows to your table allows you perform calculations using your table data. A calculated area calculates data within one document, while a totals row percreates a calculation on a whole area of information. Whenever you check out a subtotal for one record, you are looking at a calculated field. Similarly, whenever before you check out a grand also total at the bottom of a table, you're looking at a totals row.


To learn just how to create calculated areas and also totals rows, evaluation our lesboy on Creating Calculated Fields and Totals Rows.

Challenge!

Open the Products Table, and adjust the data type for the summary area to Long Text.Open the Customers Table and include a new field that supplies the Short Text data form. Name the brand-new area Credit Card.Add the following Field Validation Rule to the Credit Card field: "VS" Or "MC" Or "AMX".Set a field validation message for the exact same area. It must say Enter a valid Credit Card Type: Must be VS, MC, or AMX.

See more: Five Finger Death Punch Remember Everything Lyrics, Five Finger Death Punch

Test your area validation rule by keying Visa into a cell in the Credit Card field. If you've added the preeminence correctly, a dialog box should show up via the message you included over.