Microsoft Jet stores data using the set of data types shown in the following table. However, keep in mind that each tool you use to view or manipulate data stored in Microsoft Jet might call the data type by a different name. For example, a Boolean field in Microsoft Jet is called a Yes/No field in table Design view in Microsoft Access. In SQL, the same field can be referred to several different ways: BIT, LOGICAL, LOGICAL1, YESNO, or BOOLEAN.
| Data type | Field size | Range of values |
| Boolean | 1 byte | 0 or – 1 |
| Byte | 1 byte | 0 through 255 |
| Integer | 2 bytes | – 32,768 through 32,767 |
| Long Integer | 4 bytes | – 2,147,483,648 through 2,147,483,647 |
| Currency | 8 bytes | – 922,337,203,685,477.5808 through 922,337,203,685,477.5807 |
| Single | 4 bytes | – 3.4E-38 through 3.4E+38 |
| Double | 8 bytes | – 1.8E-308 through 1.8E+308 |
| DateTime | 8 bytes | Date and time values between the years 100 and 9999. Stored as a floating-point value. The integer portion represents the number of days since December 30, 1899. The fractional portion represents the number of seconds since midnight. |
| Text | n bytes | 1 through 255 characters, stored variable length |
| Memo | 12+ bytes | 12 bytes in the record, plus the actual data length (14 bytes in versions prior to 3.0) |
|
Long Binary (OLE Object) |
12+ bytes | 12 bytes in the record, plus the actual data length (14 bytes in versions prior to 3.0) |
| Binary | n bytes | 1 through 255 characters of binary data |
| GUID | 16 bytes | System-generated number that is guaranteed to be unique. Users should never write to this field. |
Microsoft Jet recognizes several overlapping sets of data types. In Microsoft Access, there are four different contexts in which you may need to specify a data type — in table Design view, in the Query Parameters dialog box, in SQL view in a query, and in Visual Basic.
See Also For more information on these contexts and their functionality, see Microsoft Access Help.
The following table compares the sets of data types that correspond to each context. The first column lists the Microsoft Jet data type. The second column lists the Type property settings available in table Design view and the five FieldSize property settings for the Number data type. The third column lists the corresponding query parameter data types available for designing parameter queries in the Query Parameters dialog box. The fourth column lists the corresponding SQL data types defined by Microsoft Jet along with their valid synonyms. The fifth column lists the corresponding Visual Basic data types.
| Microsoft Jet | Table fields | Query parameters | Microsoft Jet SQL and synonyms | Visual Basic |
| Boolean | Yes/No | Yes/No |
BOOLEAN (Synonyms: BIT, LOGICAL, LOGICAL1, YESNO) |
Boolean |
| Byte |
Number (FieldSize =Byte) |
Byte |
BYTE (Synonym:INTEGER1) |
Byte |
| Integer |
Number (FieldSize =Integer) |
Integer |
SHORT (Synonyms: INTEGER2, SMALLINT) |
Integer |
| Long Integer |
Number (FieldSize =Long Integer) and AutoNumber (FieldSize =Long Integer) |
Long Integer | COUNTER, INTEGER, INT, AUTOINCREMENT | Long |
| Currency | Currency | Currency |
CURRENCY (Synonym: MONEY) |
Currency |
| Single |
Number (FieldSize =Single) |
Single |
SINGLE (Synonyms:FLOAT4, IEEESINGLE, REAL) |
Single |
| Double |
Number (FieldSize =Double) |
Double |
DOUBLE (Synonyms: FLOAT, NUMBER) |
Double |
| DateTime | Date/Time | Date/Time |
DATETIME (Synonyms: DATE, TIME, TIMESTAMP) |
Date |
| Text | Text | Text |
TEXT (Synonyms: ALPHANUMERIC, CHAR, CHARACTER, STRING, VARCHAR) |
String |
| Long Text | Memo | Memo |
LONGTEXT (Synonyms: MEMO, LONGCHAR, NOTE) |
String |
| Long Binary | OLE Object | Binary, OLE Object |
LONGBINARY (Synonyms: OLEOBJECT, GENERAL) |
String |
| Binary | Not supported | Binary |
BINARY (Synonym: VARBINARY) |
String |
| GUID |
Number or AutoNumber (FieldSize =Replication ID) |
Replication ID | GUID | Not supported |
Keep in mind the following points about data types: