When we are creating a table in the Access database, we need to specify fields (columns) for the table. We can use these fields to store various types of data, including text, numbers, dates, and objects like images. We can easily manipulate and perform other operations on these fields when we specify the type of data they can accept.
There are lots of data types available in Access. In this note, we'll see some of the basic and most usable data types in Access.
Short Text
This data type stores letters, numbers, and symbols as text. The maximum length it can store is 255 characters. It can be used to store and manipulate text data, such as names, email addresses, contact numbers, NIC numbers, and cities.
Long Text
Users use this data type to store large amounts of text data. Short text limits you to 255 characters, but this type expands the field size to 65,536. This type of field can store a larger amount of text, such as descriptions or addresses, which may include characters greater than 255.
Number
We use this data type to store numeric values. Calculations frequently rely on the numbers stored in this type of field. In this data type, there are a few different sizes, such as integer (to store numbers from -32,768 to 32,767), long integer (to store numbers from -2,147,483,648 to 2,147,483,647), single & double (to store floating point numbers), and byte (to store numbers from 0 to 255). You can use number data types to store quantities, prices, and student marks.
Date Time
We use this field to store date and time values. Using this field, you can either directly type the date or pick it from the date picker. This data type has an 8-byte field size. You can store date values in an item table, such as a student's birth date, the date an employee joined a company, and a product's expiration date.
Currency
Used to store monetary values. The field has a size of 8 bytes. You can use this field to store the price of an item, an employee's salary, and a service's cost.
AutoNumber
These fields store an automatically generated number. This helps to generate unique numbers. The field size can be either a 4 bytes long integer or a 16 bytes replication ID. Duplicate-free fields are more likely to be the table's primary key.
In the next practical exercises, we'll see these data types in action.