Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 36 ยท Tables and Relationships

Access Field Data Types Explained

Learn every Access field data type: Short Text, Long Text, Number sizes, Currency, Date/Time, AutoNumber, Yes/No, Attachment, Hyperlink, Calculated and Large Number.

Choosing the right data type is one of the most important decisions you make when you design a table. The type controls what you can store, how much space it takes, how it sorts, which functions work on it and how accurate calculations are. A phone number stored as a number loses its leading zero. A price stored as a floating-point number slowly accumulates rounding errors. A date stored as text sorts in the wrong order.

This lesson walks through each Access data type, explains the Number sizes and shows how the main types map to standard SQL.

The data types at a glance

Data type Stores Size Typical use
Short Text Up to 255 characters Characters used Names, codes, phone numbers, postal codes
Long Text Up to about 1 GB (65,535 characters shown in the datasheet) Characters used Notes, descriptions, comments
Number Numeric, several sizes 1 to 16 bytes Quantities, scores, counts
Large Number 64-bit integer 8 bytes Very large whole numbers (Access 2019 and Microsoft 365)
Date/Time Dates and times 8 bytes Birth dates, order times
Date/Time Extended Higher precision dates 42 bytes Dates outside the normal range, nanoseconds
Currency Fixed-point money 8 bytes Prices, balances
AutoNumber Automatic unique integer 4 bytes (or 16 for Replication ID) Primary keys
Yes/No True or False 1 bit Flags such as Paid, Active, OnLoan
OLE Object Embedded files Up to 2 GB Legacy; avoid in new designs
Hyperlink Web or file address Up to 2,048 characters Links to documents or sites
Attachment Files attached to a record Up to 2 GB total file Photos, PDFs, scans
Calculated A value computed from other fields Depends on result Totals, full names
Lookup Wizard A list of values or a related table Depends A drop-down on the field

Text

Short Text (called Text in Access 2010 and earlier) is the default. Its Field Size property limits it from 1 to 255 characters; the default is 255. Use it for anything that you do not do arithmetic on, including phone numbers, ZIP and postal codes, product codes and ID numbers. Leading zeros survive only in text.

Long Text (called Memo before Access 2013) stores paragraphs. It cannot be indexed efficiently, so avoid filtering or sorting on it. If you want a note that the database can search quickly, store a short summary in a Short Text field.

Setting Text Format to Rich Text on a Long Text field lets users apply bold, italics and colors. The text is stored as HTML-like markup.

Numbers

A Number field has a Field Size property that sets exactly what it can hold:

Field Size Range Decimals Bytes
Byte 0 to 255 none 1
Integer -32,768 to 32,767 none 2
Long Integer about -2.1 billion to 2.1 billion none 4
Single about 7 significant digits yes 4
Double about 15 significant digits yes 8
Decimal up to 28 digits, exact yes 12

Long Integer is the default and a good everyday choice. Use Byte for small counts like a rating from 1 to 5. Use Double for scientific measurements where tiny rounding differences do not matter. Use Decimal (set Precision and Scale) when you need exact decimal arithmetic.

Why floating-point numbers surprise you

Single and Double store numbers in binary. Many decimal fractions, like 0.1, have no exact binary form, so sums can drift in the last digits. Adding 0.1 and 0.2 is the classic demonstration, in any language:

SQL Floating point versus exact cents

Press Run (or Ctrl + Enter) to see the result here.

The floating sum prints as 0.30000000000000004 and is not equal to 0.3 (the comparison returns 0, meaning false), while the whole-number sum is exact. Access solves this for money with the Currency type, a fixed-point number with four decimal places and 15 digits before the point.

Currency

Use Currency for money. It is exact to four decimal places, so adding thousands of prices gives the same total every time. It also formats with your Windows currency symbol. The Decimal number size is the alternative if you need more places. Do not use Single or Double for money.

Dates and times

Date/Time stores both a date and a time in one value, internally as a Double: the integer part counts days since December 30, 1899 and the fraction is the time of day. You can enter dates the way your Windows regional settings expect (3/5/2026 is March 5 in the US and 3 May in the UK), so prefer an unambiguous format like 2026-03-05 when typing.

Useful properties:

  • Format: Short Date, Long Date, Medium Date, Short Time, General Date or a custom pattern like yyyy-mm-dd.
  • Show Date Picker: displays a calendar next to the field.

If you only need a date, you can leave the time empty. Midnight is stored as the fraction 0. A pitfall: a value that holds a time, such as 2026-03-05 14:30, is not equal to the date 2026-03-05 in a criteria comparison. Use >= #3/5/2026# And < #3/6/2026# for a whole day. You will practice this in the criteria lesson.

Date/Time Extended (Access 2019 and later) holds years 0001 to 9999 and higher precision, but fewer functions accept it. Use plain Date/Time unless you really need the range.

AutoNumber

AutoNumber creates a new unique value for every record. The default New Values is Increment, which counts 1, 2, 3 and so on. Random gives unpredictable numbers. You cannot edit an AutoNumber, and deleted numbers are not reused, so gaps are normal. Treat it as an internal key, not a meaningful number such as an invoice number the customer sees.

Yes/No

A Yes/No field holds True or False. It is shown as a check box, and in queries you can write True, False, Yes, No, -1 or 0. Use it for flags. If you find yourself wanting a third answer (unknown), use a Number or Short Text field with a list of choices instead, or use a Null for "not answered" (Yes/No in Access cannot hold Null unless you turn on Triple State in a form control).

  • Hyperlink stores a web address, email address or file path with optional display text. It is clickable in forms.
  • Attachment lets one field hold several files per record. Access compresses some formats, such as images, but files still count toward the 2 GB limit. Attachments cannot be used in SQL SELECT the way text can: you query them through FieldName.FileName and FieldName.FileData.
  • OLE Object embeds files in an older format. It bloats the file and is tied to Windows applications. Prefer Attachment, or store file paths in a Short Text or Hyperlink field.

For large collections of images or documents, store them on disk or SharePoint and keep only the path in Access.

Calculated fields

A Calculated field stores an expression based on other fields in the same table, such as [Quantity]*[UnitPrice]. You write the expression in the Expression Builder when you pick the type. The value is saved, updates automatically and can be used in queries.

Calculated fields are convenient but have limits: they cannot refer to other tables or use user-defined functions, and some purists argue you should calculate on demand in a query instead, so your data never stores something that can be derived. For a quick total, both approaches are acceptable. In larger databases, put calculations in queries.

Lookup Wizard

The Lookup Wizard is not a real data type. It adds a combo box to a field that fills from either a typed list or another table. The stored value is still the underlying type (usually a number key). It is covered in the field properties lesson.

Mapping Access types to SQL

Access type Access SQL Standard SQL
Short Text TEXT(n), VARCHAR(n) VARCHAR(n)
Long Text MEMO, LONGTEXT TEXT
Number (Long Integer) LONG, INTEGER INTEGER
Number (Double) DOUBLE, FLOAT DOUBLE PRECISION
Currency CURRENCY, MONEY DECIMAL(19,4)
Date/Time DATETIME, DATE TIMESTAMP
AutoNumber COUNTER, AUTOINCREMENT GENERATED ... AS IDENTITY
Yes/No YESNO, BIT BOOLEAN

SQLite is flexible about types, but it still sorts and compares values by their storage class. This example shows why storing numbers as text goes wrong:

SQL Text sorts differently from numbers

Press Run (or Ctrl + Enter) to see the result here.

Text sorts character by character, so 10 and 100 come before 2 and 9. The same trap happens in Access if you store quantities or IDs as Short Text.

Choosing a type: a quick guide

  • Phone numbers, ZIP codes, product codes, social IDs: Short Text.
  • Counting items: Number (Long Integer).
  • Money: Currency.
  • Dates and times: Date/Time.
  • Yes or no: Yes/No.
  • Free notes: Long Text.
  • Files: Attachment, or a path in Short Text.
  • Unique record number: AutoNumber.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You store postal codes such as 02134. Which type keeps the leading zero?

    Numbers drop leading zeros. Codes you never calculate with belong in Short Text.
  2. 2. Which type should you use for prices to avoid floating-point rounding problems?

    Currency is a fixed-point type with four decimal places, so totals stay exact.
  3. 3. What is the default Field Size for a Number field?

    Long Integer is the default Number size, holding whole numbers from about -2.1 billion to 2.1 billion.
  4. 4. Why is it risky to rely on an AutoNumber as a visible invoice number?

    AutoNumber is unique but not guaranteed to be sequential. Use it internally and generate a separate invoice number if it must be gap-free.

Summary

  • Short Text holds up to 255 characters, Long Text holds notes, Number has sizes from Byte to Decimal and Currency is exact money.
  • Date/Time stores both date and time; a value with a time part will not equal a plain date in a comparison.
  • AutoNumber gives unique keys, Yes/No holds flags, Attachment holds files and Calculated fields store expressions.
  • Use text for codes and IDs, numbers for arithmetic and Currency for money.
  • Access SQL uses names such as COUNTER, YESNO, CURRENCY and DATETIME.

Next: Field Properties, Validation and Lookups