Why would a field that contains numbers have a text data type?

In order to continue enjoying our site, we ask that you confirm your identity as a human. Thank you very much for your cooperation.

Microsoft Access database fields are created by entering a field name and a field data type in each row of the field entry area of the database table window. The field description is an option to identify the fields purpose; it appears in the status bar during data entry. After you enter each field's name and data type, you can specify how each field is used by entering properties in the property area. Before you enter any properties, however, you should enter all of you fields names and data types.

Naming a Database Field

A field name identifies the field to both you and to Microsoft Access. For information regarding field names please see the article: Tables and Fields

Specifying a Microsoft Access Data Type

After you name a field, you must decide what type of data the field will hold. Before you begin entering data, you should have a grasp of the data types that your system will use. Ten basic data types are shown in the table below; some data types (such as numbers) have several options:

Microsoft Access Data Types

Data Type	Use For	Size
Text	Text or combinations of text and numbers, such as addresses. Also numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.	Up to 255 characters. Microsoft Access only stores the characters entered in a field; it does not store space characters for unused positions in a Text field. To control the maximum number of characters that can be entered, set the FieldSize property.
Memo	Lengthy text and numbers, such as notes or descriptions.	Up to 64,000 characters.
Number	Numeric data to be used for mathematical calculations, except calculations involving money (use Currency type). Set the FieldSize property to define the specific Number type.	1, 2, 4, or 8 bytes. 16 bytes for Replication ID (GUID) only. Click here for more information on changing Number field sizes.
Date/Time	Dates and times.	8 bytes.
Currency	Currency values. Use the Currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right.	8 bytes.
AutoNumber	Unique sequential (incrementing by 1) or random numbers automatically inserted when a record is added.	4 bytes. 16 bytes for Replication ID (GUID) only.
Yes/No	Fields that will contain only one of two values, such as Yes/No, True/False, On/Off.	1 bit.
OLE Object	Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol, that can be linked to or embedded in a Microsoft Access table. You must use a bound object frame in a form or report to display the OLE object.	Up to 1 gigabyte (limited by disk space).
Hyperlink	Field that will store hyperlinks. A hyperlink can be a UNC path or a URL.	Up to 64,000 characters.
Lookup Wizard	Creates a field that allows you to choose a value from another table or from a list of values using a combo box. Choosing this option in the data type list starts a wizard to define this for you.	The same size as the primary key field that is also the Lookup field; typically 4 bytes.

For numeric data types, the field size enables you to further define the type of number, which in turn determines the storage size. The table below shows the seven possible settings in the Numeric Field Size property.

You should make the field size the smallest one possible; Access runs faster with smaller field sizes. Note the first three settings don’t use decimal points, but allow increasingly larger positive or negative numbers. Single and Double permit even larger numbers: Single gives you 7 decimal places, and Double allows 15. Use the Double setting when you need many decimal places or very large numbers.

Numeric Field Size Properties

Setting	Description	Decimal Precision	Storage Size
Byte	Stores numbers from 0 to 255 (no fractions).	None	1 byte
Integer	Stores numbers from –32,768 to 32,767 (no fractions).	None	2 bytes
Long Integer	(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).	None	4 bytes
Decimal	Stores numbers from -10^38 -1 through 10^38 -1 (.adp) Stores numbers from-10^28 -1 through 10^28 -1 (.mdb)	28	12 bytes
Single	Stores numbers from –3.402823E38 to –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.	7	4 bytes
Double	Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values.	15	8 bytes
Replication ID	Globally unique identifier (GUID)	N/A	16 bytes

Caution: If you convert a large FieldSize setting to a smaller one in a field that already contains data, you might lose data. For example, if you change the FieldSize setting for a Text data type field from 255 to 50, data beyond the new 50-character setting will be discarded.

If the data in a Number data type field doesn't fit in a new FieldSize setting, fractional numbers may be rounded or you might get a Null value. For example, if you change from a Single to an Integer field size, fractional values will be rounded to the nearest whole number and values greater than 32,767 or less than –32,768 will result in null fields.

Tip: You can use the Currency data type if you plan to perform many calculations on a field that contains data with one to four decimal places. Single and Double data type fields require floating-point calculation. Currency data type fields use a faster fixed-point calculation.

The following table lists the data types available in desktop databases in Access 2013 and later versions.

Data Type	Usage	Size
Short Text (formerly known as “Text”)	Alphanumeric data (names, titles, etc.)	Up to 255 characters.
Long Text (formerly known as “Memo”	Large amounts of alphanumeric data: sentences and paragraphs. See The Memo data type is now called “Long Text” for more information on the Long Text details.	Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.
Number	Numeric data.	1, 2, 4, 8, or 16 bytes.
Large Number	Numeric data.	8 bytes. For more information, see Using the Large Number data type.
Date/Time	Dates and times.	8 bytes.
Date/Time Extended	Dates and times.	Encoded string of 42 bytes For more information, see Using the Date/Time Extended data type.
Currency	Monetary data, stored with 4 decimal places of precision.	8 bytes.
AutoNumber	Unique value generated by Access for each new record.	4 bytes (16 bytes for ReplicationID).
Yes/No	Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.	1 byte.
OLE Object	Pictures, graphs, or other ActiveX objects from another Windows-based application.	Up to about 2 GB.
Hyperlink	A link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer	Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).
Attachment	You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats.	Up to about 2 GB.
Calculated	You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn't available in MDB file formats.	Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.
Lookup Wizard	The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.	Dependent on the data type of the lookup field.

The following table lists the data types available in desktop databases in Access 2010 and Access 2007.

Data Type	Usage	Size
Text	Alphanumeric data (names, titles, etc.)	Up to 255 characters.
Memo	Large amounts of alphanumeric data: sentences and paragraphs.	Up to about 1 gigabyte (GB), but controls to display a long text are limited to the first 64,000 characters.
Number	Numeric data.	1, 2, 4, 8, or 16 bytes.
Date/Time	Dates and times.	8 bytes.
Currency	Monetary data, stored with 4 decimal places of precision.	8 bytes.
AutoNumber	Unique value generated by Access for each new record.	4 bytes (16 bytes for ReplicationID).
Yes/No	Boolean (true/false) data; Access stores the numeric value zero (0) for false, and -1 for true.	1 byte.
OLE Object	Pictures, graphs, or other ActiveX objects from another Windows-based application.	Up to about 2 GB.
Hyperlink	A link address to a document or file on the Internet, on an intranet, on a local area network (LAN), or on your local computer	Up to 8,192 (each part of a Hyperlink data type can contain up to 2048 characters).
Attachment	You can attach files such as pictures, documents, spreadsheets, or charts; each Attachment field can contain an unlimited number of attachments per record, up to the storage limit of the size of a database file. Note, the Attachment data type isn't available in MDB file formats.	Up to about 2 GB.
Calculated	You can create an expression that uses data from one or more fields. You can designate different result data types from the expression. Note, the Calculated data type isn't available in MDB file formats. Note: The Calculated data type is not available in Access 2007.	Dependent on the data type of the Result Type property. Short Text data type result can have up to 243 characters. Long Text, Number, Yes/No, and Date/Time should match their respective data types.
Lookup Wizard	The Lookup Wizard entry in the Data Type column in Design view is not actually a data type. When you choose this entry, a wizard starts to help you define either a simple or complex lookup field. A simple lookup field uses the contents of another table or a value list to validate the contents of a single value per row. A complex lookup field allows you to store multiple values of the same data type in each row.	Dependent on the data type of the lookup field.