A number field in a database can contain values such as age, quantity or any number for calculations. It is very vital to have number fields defined for columns used in calculations. You cannot do many of the computational functions such as average, sum etc., if you do not define the field as a number field.

How to create the field?

  • Open MS Access You have to open the table in Design view. If you want to create a field in between, click on the field after which you want your number field to appear. You can also create the number field as the last field in the table.
  • Click on Insert and then rows which will bring a row with field name, data type and description. You can also click directly on the insert row icon.
  • On the first column, Field Name, enter the name of your choice as the field name for your number like ‘Age’ or Identification number or anything you wish to call it.
  • When you click on Data Type, A pull down menu appears with different data types like text, number, Date/Time, and so on. Choose Number from this and size required from the box below.
  • Save your table

More options to define the Number field

After you choose the Data type as Number, in the box below you have more options some of which are really useful for number fields.

Field Size

There is a pull down menu for Field size, in the pull down menu there are options such as Single, Integer, Long Integer, Byte, Double, Replication ID and Decimal. You have to choose this depending on your use for the number you are storing. You can enter the precision required for a Decimal field in the precision box. If you want any default value to be entered in the field when you don’t have a value for this field in a specific row that can be given in the ‘Default Value’.

To enforce Validation

If you have any restriction on the value for the field you have just defined, such as the field should contain only numbers from say 1 to 10, you can use the validation field to establish this rule for the particular field. When you click on the validation rule, a small box appears at the end of the field and when you click on that it will bring up the expression builder box where you can enter the rule of validation you want to enforce. You can even build functions to check for the validity. In the “Validation Text” field you can enter the text message that you want to display to the user when they enter a number which is prohibited by the validation rule.

Index Field

You can choose to make the field you just defined as an index field. Also you can choose whether duplicate values are allowed in this field or not.