# JET Database/Data types
## Quick Reference

| JET type | VBA type | Size | Values |
| byte, tinyint, integer1 | Byte | 1 byte | integers 0 to 255 |
| smallint, short, integer2 | Integer | 2 bytes | integers -32,768 to 32,767 |
| integer, int, long, integer4 | Long | 4 bytes | integers -2,147,483,648 to 2,147,483,647 |
| single, real, float4, IEEESingle | Single | 4 bytes | -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values |
| double, number, double precision, float, float8, IEEEDouble | Double | 8 bytes | -1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values |
| currency, money | Currency | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| decimal, numeric | | 17 bytes | fixed precision decimal numbers |
| counter, identity, autoincrement | Long | 4 bytes | auto-incrementing integer for surrogate keys |
| bit, yesno, logical, logical1 | Boolean | 1 bit | 0 or 1 (may appear as 0 or -1 sometimes) |
| datetime, date, time | Date | 8 bytes | 1 January, 100 to 31 December, 9999 with optional time information |
| text, varchar | String | <= 255 characters | variable-length text strings, with Unicode support since JET 4.0 |
| char | String | <= 255 characters | fixed-length text strings, with Unicode support since JET 4.0 |
| longchar, memo | Memo | <= 2 GB | variable length text BLOB |
| binary, binary varying | Binary | <= 510 bytes | fixed-length binary data |
| longbinary, image, general, oleobject | Binary | <= 2 GB | variable length BLOB |
| guid | Binary | 16 bytes | uniqueidentifier |

## Text types

There are two basic data types, plus a BLOB type, that are used to hold text values in JET databases. Prior to JET 4.0 they could only hold 8-bit text characters, but databases created with an Engine Type code of 5 or greater (i.e. JET 4.0) can also store Unicode characters.
Since JET 4.0, text values that are primarily one byte (8-bit) text characters are stored in two bytes unless otherwise specified. This increases the storage required for all text data, effectively doubling it. JET 4.0 has an additional property that can be set on text columns to compress text data to one-byte characters when it is able to. This property is set by specifying WITH COMPRESSION on each text column that requires it. When a table is created in a JET database using Microsoft Access, text columns are normally created with this setting turned on (as Unicode compression).

### Fixed-length text

The char data type is used to store fixed-length text with up to 255 characters. Specifying the number of characters to store limits how big the column will be. Text values retrieved from a char column are padded with spaces, if necessary, to the size of the column.
The char data type is not available from the Access designer.
The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

### Variable-length text

The varchar data type is used to store variable-length text with up to 255 characters.Text values retrieved from a varchar column are trimmed of any trailing spaces.
The following statement creates a table with a 10-character text column and a 255-character text column, both with Unicode compression:

### Text BLOB

The longchar data type is used to store variable-length text with an unspecified number of characters, limited only by the maximum size of JET database files (2 GB – about 1 billion uncompressed Unicode characters).
Some software libraries are able to handle longchar columns as basic text columns, but others must use BLOB techniques for accessing their data. In particular, the ADO components so often used in Visual Basic, VBA and ASP applications can access longchar columns as basic text when using the JET 4.0 OLE-DB provider to access the database, but must use BLOB handling routines (GetChunk / AppendChunk) when using an ODBC connection.
The following statement creates a table with two variable-length text BLOB columns, both with Unicode compression:

### Text pseudo-types

The text data type is a pseudo-type. When a size is specified, it is taken to be a varchar and is limited to 255 characters. When no size is specified, it is taken to be a longchar.
The following statement creates a table with a 10-character text column and a variable-length text BLOB column, both with Unicode compression:

## Numeric types

There are several basic numeric data types available in JET, allowing for a wide range of data storage possibilities for numeric values.

### Byte

The byte data type is an 8-bit unsigned integer that can hold values between 0 and 255 inclusive. It can be referred to by the following aliases: byte, tinyint, integer1.
It is analogous with the Visual Basic type Byte.

### Smallint

The smallint data type is a 16-bit (2-byte) signed integer that can hold values between -32,768 and 32,767 inclusive. It can be referred to by the following aliases: smallint, short, integer2.
It is analogous with the Visual Basic type Integer.

### Integer

The integer data type is a 32-bit (4-byte) signed integer that can hold values between -2,147,483,648 and 2,147,483,647 inclusive. It can be referred to by the following aliases: integer, int, long, integer4.
It is analogous with the Visual Basic type Long.

### Single

The single data type is a 32-bit (4-byte) single precision floating point number that can hold values from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. It can be referred to by the following aliases: single, real, float4, IEEESingle.
It is analogous with the Visual Basic type Single.

### Double

The double data type is a 64-bit (8-byte) double precision floating point number that can hold values from -1.79769313486231E308 to -4.94065645841247E-324 for negative values, and 4.94065645841247E-324 to 1.79769313486232E308 for positive values. It can be referred to by the following aliases: double, double precision, number, float, float8, IEEEDouble.
It is analogous with the Visual Basic type Double.

### Currency

The currency data type is a 64-bit (8-byte) exact precision decimal number (implemented as a scaled integer) that can hold values between -922,337,203,685,477.5808 and 922,337,203,685,477.5807 inclusive. It can be referred to by the following aliases: currency, money.
It is analogous with the Visual Basic type Currency.

### Decimal

The decimal data type is a 17-byte exact precision decimal number that can hold values from -10$^{28}$-1 to 10$^{28}$-1. It can be referred to by the following aliases: decimal, dec, numeric.

## Boolean (Yes/No)

JET supports a data type called bit that can hold one bit of information, used for holding simple Boolean states of true or false. It can only hold the values 0 or 1, but some interfaces into the JET database present these as 0 and -1, or No and Yes. It can be referred to by the following aliases: bit, logical, logical1, yesno.
It is analogous with the Visual Basic type Boolean.

## Dates and times

The datetime data type is a 64-bit (8-byte) double precision floating point number that can hold both date and time information, in the range 1 January, 100 to 31 December, 9999 and with optional times in hours, minutes, seconds. It can be referred to by the following aliases: datetime, date, time, all capable of holding just date, just time, and both date and time information.
It is analogous with the Visual Basic type Date.
To determine whether a datetime holds time information, check to see whether the double precision number is the same as the integer truncation of that number; if they differ, then there is time information, otherwise there is no time information.

## Counters

The counter data type is an auto-incrementing 32-bit (4-byte) integer, often used for creating surrogate keys. It can be referred to by the following aliases: counter, autoincrement, identity.
A counter can take optional parameters, defining the initial value to start counting at, and the increment to add each time a new value is created. The following code shows both the default, starting at one with increment of one, and with specified start of 10 and increment of 2. Each table can have, at most, one column of type counter.

## BLOBs

JET can store binary large objects, or BLOB data, in the database directly as binary data. Accessing this data typically requires special BLOB handling functions, for example, ADO provides the functions GetChunk and AppendChunk for reading and writing BLOB data.

### Binary

The binary data type is a variable-length binary object type that can hold up to 510 bytes of binary data. If the size is not specified, then the maximum size of 510 bytes is used. It can be referred to by the following aliases: binary, binary varying.

### Longbinary

The longbinary data type is a variable-length binary object type with an unspecified capacity, limited only by the maximum size of JET database files (2 GB). It can be referred to by the following aliases: longbinary, general, image, oleobject.