Access Data Types: Choosing the Right One, and What Goes Wrong

Choosing a field's data type takes two seconds and is close to permanent. Get it wrong and you find out months later, when leading zeros have vanished from every postcode, or the totals on a report are a penny out and nobody can say why.

This is a guide to picking the right one, and to the specific damage each wrong choice causes.

A note on names

If you learned Access years ago, two names changed in Access 2013 and older guides still use the old ones: Text is now Short Text, and Memo is now Long Text. Same fields, new labels.

Text

Short Text holds up to 255 characters and is the right default for most fields. Names, addresses, references, and — importantly — anything numeric that you will never do arithmetic on.

That last point is the single most common data type mistake in Access. Phone numbers, postcodes, account references and product codes are text, not numbers. Store a postcode as a Number and a leading zero disappears the moment it is saved; store a phone number as a Number and you cannot keep a space, a plus sign or a leading zero. The test is simple: would you ever add two of them together? If not, it is text.

The Field Size setting on a text field is worth using. Setting a country code field to 2 characters stops a whole class of bad data at entry, and costs nothing.

Long Text holds up to about a gigabyte and is for genuine free text — notes, descriptions, correspondence. It comes with real costs: you cannot index it, sorting and grouping only look at the first 255 characters, and it inflates the file. Use it where you need it and not as a lazy default for "text that might be long".

Numbers

Number has a Field Size setting that matters more than the type itself:

• Long Integer — whole numbers to about ±2.1 billion. This is the correct setting for a field that links to an AutoNumber primary key, and getting it wrong is why a relationship sometimes refuses to be created.
• Integer — whole numbers to ±32,767. Small, and easy to overflow without noticing.
• Byte — 0 to 255 only.
• Single / Double — decimals, stored as approximations.
• Decimal — exact decimals, but awkward in Access and rarely the best answer.

Never use Single or Double for money. They store approximate values, so 0.1 is not exactly 0.1. Add a few thousand of those together and the total drifts from the invoices by a penny or two — small enough that nobody spots it for a year, and impossible to explain to an accountant.

Currency

Use this for money, always. It stores exact values to four decimal places, so totals are correct, and it formats itself. There is no situation where Double is the better choice for a monetary amount.

Date/Time

Use a real Date/Time field for dates. Never a text field: text sorts alphabetically, so "10/01/2026" comes before "02/03/2026", and date arithmetic becomes impossible.

Access stores a date and a time together whether you want both or not, and this causes a specific and very common bug. A field displayed as a date still holds a time behind the scenes, so a record saved at 14:30 does not match a filter of "equals today" — the stored value is today plus a time. If a query is mysteriously missing today's records, this is why. Filter with >= Date() And < Date()+1, or strip the time on entry with DateValue().

Newer versions also offer Date/Time Extended, which exists mainly for compatibility with SQL Server's datetime2. Do not use it unless you specifically need that.

Yes/No

For genuine two-state facts: paid, active, archived. Two things to know. It cannot be null — it is one or the other, so there is no "not answered yet"; if you need three states, use a Short Text field with defined values. And behind the display, Yes is stored as -1 and No as 0, which surprises people writing expressions.

AutoNumber

For primary keys, and effectively only for primary keys. It generates a unique number and never reuses one.

Two things people expect that are not true: the numbers will have gaps — a cancelled entry consumes a number permanently — and they are not a sequence you should show anyone. If you need a gapless invoice number, generate it yourself in a separate field and keep the AutoNumber as the internal key.

Calculated fields, and why to avoid them

Access lets you store a calculated field on a table — quantity times price, for instance. It is tempting and it is usually the wrong place for it.

Calculations belong in queries, where they are always current and visible to anyone reading the query. Stored on the table, they are invisible, they complicate any future move to SQL Server (which has no direct equivalent to Access's version), and they encourage storing values that could be derived. The one legitimate exception is a figure that must be frozen as it was at the time — the price actually charged on an order line — and that should be a plain field the process writes, not a calculated one.

Attachment and OLE Object: don't

OLE Object is a legacy type that bloats the file dramatically and should be considered dead.

Attachment works and is genuinely convenient, but it stores the files inside the database, and Access has a hard 2GB ceiling. A few hundred scanned invoices will end that database's life. Store a file path or a link instead and keep the documents on the file server where backups already cover them. This is one of the most common reasons a working Access database suddenly hits the size limit.

Lookup fields: don't, either

The Lookup Wizard offers to make a field show values from another table. It stores one value and displays a different one, which means queries return numbers where you expected names, other developers cannot tell what is really stored, and migration later is painful.

Do the same job properly: a plain Number field holding the key, a relationship, and a combo box on the form. Same convenience for the user, none of the hidden behaviour.

Changing a type after the data is in

Sometimes necessary, occasionally destructive. Access converts what it can and silently discards what it cannot — changing Short Text to Number turns "N/A" into an empty cell without asking.

The safe order: copy the file first; add a new field of the correct type; run an update query to populate it, checking what fails to convert; verify the counts match; then repoint forms and reports and delete the old field. Never convert in place on live data during working hours.

Why this matters beyond Access

Data types are the part of a database design that transfers directly. If you ever move to a proper server database — because the file is nearing 2GB, too many people need it at once, or it must be reachable from a browser, none of which Access can fix — well-chosen types map straight across. Currency becomes a decimal column, Long Integer keys become integer keys, and the migration is mechanical.

A database where everything is Short Text because that always worked is a much longer conversation, because someone has to decide what each field really is before anything can move.

Our Microsoft Access services page covers both improving what you have and moving beyond it, and a free system review will tell you which one you need.