Field Types in Microsoft Access TEXT data type The Memo data type is now called “Long Text”

MDB / ACCDB Viewer

Open Microsoft Access Databases on your Mac

About ‧
App Store Reviews ‧
Documentation

MDB Viewer

Download

Buy Now

Also available on the
Mac App Store

MDB Viewer requires OS X 10.6 or later.
The free trial is fully functional except for export limitations.
© 2011-2017 Jakob Egger

Help Index

Field types in Microsoft Access

Fields in a Microsoft Access database can be of many different data types.
Access supports different kinds of text, numbers, dates and some more special data types.
This document will describe each data type and note which versions of Microsoft Access support it.

Textual types

Text
The Text data type is used for short text fields of limited length, and can contain up to 255 characters.
Memo
A Memo field is a virtually unlimited text field. It can store up to 1GB of text.

Microsoft Access supports text in a variety of languages and alphabets.
Access 97 supported multiple code pages in the same document. This worked great in most cases,
but caused problems when columns contained text in different alphabets.
Therefore Microsoft switched to the Unicode character encoding in Access 2000.
MDB Viewer automatically supports all encodings used in Access.

Numeric Types

Byte, Integer and Long Integer
Integers in Access come in 1, 2 and 4 byte varieties.
The single byte number is named Byte (Range 0-255), the two-byte number is named Integer (-32768 to 32767)
and then there is the Long Integer (-2 billion to 2 billion).
Decimal
Decimal numbers are so called fixed point numbers. They can have a fractional part,
and they can be either positive or negative. Decimals have a fixed number of digits to left and
to the right of the decimal point. The maximum total number of digits supported by Access is 28.
Decimals are used for exact numeric values. Despite the name “Decimal” you can also use the decimal data
type for integers larger than the range supported by the Long Integer type.
Decimals are only supported by Microsoft Access 2000 and later.
A decimal number uses 17 bytes of disk space.
Currency
The currency type is a special kind of decimal, with up to 4 digits on the right of the decimal point and up to 15 on the left.
It was introduced for financial data and is available in all versions of Access.
This type uses 8 bytes of disk space.
Float and Double
Floating point numbers are similar to numbers written in scientific notation, and come in two varieties: float and double. They have a precision of up about 7 decimal digits (float) or 15 digits (double).
The exponent (location of decimal point) is stored separately, an can range from -45 to +38 (float) or from -324 to 308 (double). Use floating point numbers if you require a large range but only limited precision. Floats use 4 bytes, and doubles use 8 bytes of disk space.

Other Types

Date/Time
Specifies a specific point in time (Date and time of day).
Dates are internally stored as an 8 byte double precision floating point numbers, so the range is virtually unlimited.
(Dates up to 2 million AD can be stored with a precision of 1 second.)
MDB Viewer exports dates in the format YYYY-MM-DD HH:MM:SS.
Yes/No
A boolean value that can be either on or off, displayed as a check box in Microsoft Access. Displayed as 0 or 1 in MDB Viewer.
OLE Object
An OLE object field can contain documents, such as images, text files, Microsoft Word files or Microsoft Excel files. The size of this field is limited to 1 GB.
MDB Viewer for Mac supports many kinds of OLE objects, such as Images, Word documents and PDF files.
Sometimes Access stores OLE Object in an unknown data structure that I do not understand yet. These files
show up as unknown files.
You can extract OLE files by dragging the icon to the Finder.

Complex Types

Microsoft Access 2007 introduced a number of types they call Complex Columns.
These types are multivalued fields, attachments, and version history for memo fields.
Common to all these fields is that they can contain multiple values per row, seemingly against the rules of database normalisation.
However, Access internally stores these values in normalized tables that are hidden from the user.
MDB Viewer makes these hidden tables accessible for easy exporting.

Multi-Valued Field
A multi-valued field can contain multiple numbers of any of the types listed above.
The actual value stored in the complex field is a key that links to the numbers stored in the helper table.
Attachments
Attachment fields are conceptually similar to multi-valued fields, except that the helper table contains
files rather than numbers.
Memo version history
For these fields, the helper table contains the previous contents of a field together with a time stamp.

For an example of working with multi-valued fields, see the section on viewing Access databases .

Help Index

Questions? Contact
Jakob Egger .

EGGER APPS

  〜  
Products
  〜  
About Egger Apps
  〜  
Privacy
  〜  
Contact: jakob@eggerapps.at

Tekstenuitleg.net

  • Software
  • Networking

Contents

  • Data types in Microsoft Access

Data types in Microsoft Access

Databases consist of tables, tables consist of fields and fields
are of a certain data type. A field’s data type
determines what kind of data it can hold. Every database has
data types for text, integers (integers are whole numbers, i.e.
numbers without a fraction) and decimal numbers and so does Access.
But Access also has some data types that are specific to Access,
like the Hyperlink, Attachment and Calculated data type. In this
article I will describe the Access data types and give an
explanation of their use.

Choosing data types for your fields

When you create tables in Design View, 
Access lets you choose data types for your fields. you can
access Design View by right clicking your table in the object list
on the left of the screen and choosing Design View.

Access data types

A field of type Number must contain
numbers. If you try to put in text, Access won’t like it and it
will let you know.

Wrong data type

Overview of Access data types

When you start using Access it is sometimes difficult to figure
out what data type to use for a field. The table below gives you a
general description of each data type in Access and situations
where you would use the data type.

Access data typeUsed for
Text Short (< 255 characters) like names, article titles, zip
codes, short descriptions, etc
Memo Long texts like article body text, blog posts, long
descriptions
Number Quantity, size, length, weight, speed, scores, percentages
Date/Time Birth date, order date, expiration date, creation date,
subscription date
Currency Prices
AutoNumber Primary key fields
Yes/No Yes/No situations like ‘wants to receive newsletter or doesn’t
want to receive newsletter’, ‘product is on offer or product is not
on offer’, etc.
OLE objectUsed to embed or link to documents from other programs like
Excel and Word.
Hyperlink Used for hyperlinks
Attachment Used to store files in an Access database. The attachment data
type lets you store one or more files per record.
Calculated Calculated fields allow you to store the result of a
calculation that includes data from other fields. Storing data that
can be derived from other fields should however be avoided in
database design. The use of calculated field is not
recommended.
Lookup wizard..A lookup wizard field lets the user choose from a predefined
set of options, like a “male” or “female” selection or a “country”
selection. 

Choose your data types wisely, and at the right time

Choosing the right data type for your fields is not rocket
science, but it’s best to get it right the first time, because
changing a data type later  when your table contains data can
be tricky. If you try to change a field’s data type when the table
contains data for that field, than Access has to convert that data
to the new data type and that is not always possible.

If, for example, your table contains a Text
field that contains only numbers, Access will have no problem
converting that field to the Number data type. But
Access won’t be able to convert a Text field to a Number field when
the field contains non-numeric data like “hello, how are you” or
“123ABC” or “I’m really craving a peanut butter jelly sandwich, but
I am to lazy to get of the couch”. Because there is no way for this
data to be converted to numbers.

So, choose the right data type at the moment you create your
tables. It could save you some trouble later on.

Field properties

A field’s data type does not only determine the type of data you
can put in. It also determines the
properties you can set on the field as you
will see in the next sections. 

The Text data
type

The Text data type is used very often for
names, descriptions, titles, et cetera. Text fields can contain a
maximum of 255 characters of text.  

Below is a picture of the Field Properties panel for a Text
field. The Field Properties can be found by viewing your table in
Design View and selecting one of its fields.
The Field Properties are at the bottom of the screen.

Text field data type properties

Properties for the Text field

The most important properties of the Text are below.

PropertyExplanation
Field SizeThe maximum number of characters you can enter. The largest
maximum you can set is 255.
FormatRules for the display of this field in the datasheet
Input maskIn input mask is a
pattern that all data entered in this field must adhere to
CaptionWhen a caption text is entered, it is displayed as a field
label in forms. If nothing is filled out, the filed name is used
for this purpose.
Default valueThe default value
is inserted by Access automatically when no other value is supplied
for the field.
Validation ruleA rule that validates the input. Read more on validation rules
in the validation rules
tutorial .
RequiredIndicated whether a value is required for
this field. Access will show an error message and won’t insert the
new record if you try to insert a new record that has no value for
a required field.
Allow Zero LengthIndicates whether you are allowed to insert a text that
contains zero characters. 
IndexedApply an index when you want to allow for fast searching on the
field’s contents or when you want to impose a unique constraint
(Select “Yes, no duplicates”) on the field.

 

The Memo data
type

The Memo data type is meant to hold text data,
just like the Text data type. In contrast to the Text data type,
Memo doesn’t have a 255 character size limit. The Memo data type
can hold somewhere around 65000 characters if you enter them
through the Access interface and 1 Gigabytes if you enter the text
programatically.

In Access 2007 and 2010, fields of the Memo data type can be
configured to contain Rich Text. Rich Text is text to which
formatting like bold and
italic can be applied. In the background Access
converts this styling to HTML.

The properties of the Memo data type are very similar to the
field properties of the Text data type, so I won’t describe them
again.

Memo field properties

The Text Format property lets you choose
between Plain Text and Rich Text.

 

The Number
data type

The Number data type in Access is a data type
for different types of numbers, like Integer, Long Integer, Byte,
Decimal, et cetera. The exact number type is configured using the
Field Size property.

When creating a new Number field the Field Size property
defaults to Long Integer. So by default, the Number field can
contain whole numbers.

Number field sizes

The Field Size property of the Number data
type lets you choose a number type for the field. Below are the
characteristics of each type.

Field SizeExplanationPrecision
ByteA Byte field allows whole numbers from 0 to 255.
IntegerThe Integer field stores whole numbers between -32.768 and
32.767.
Long IntegerThe Long Integer field stores whole numbers
between -2.147.483.648
and -2.147.483.647.
DecimalThe Decimal field is used for storing decimal values. It has
the largest precision of all Number fields. The properties of the
Decimal field will let you choose the total precision (max 28) and
the number of decimal places.
28
SingleFor numeric floating point values that range from -3.4 x
10
38  to +3.4 x
10
38. The Single data type has a precision of 7
and is best not used for calculations that require a high level of
precision.
7
DoubleThe Double  data type stores numbers
between -1.797 x 10
308 to +1.797 x
10
308
12
Replication IDA replication ID is a long alphanumeric string that is
used in database replication.

The most common Field Size values are probably Long Integer and
Double. 

 

The
Date/Time data type

The Date/Time data type is used to store date values, time
values, or combined date and time values. In the background Access
always stores the date and time you enter like a number. Thedate
format you specify determines what part of the date and time
is shown in the Access data sheet.

In the field properties of a Date/Time
field you can pick a format for your
date field.

Date/time formats

Note that what appears in the
Format select list is dependent on your
computer’s region and language settings. The default date format in
Windows on my computer is set to Dutch. That is why the
Long date format is “dinsdag 19 juni”
(tuesday, june 19th). Access will automatically show the date
formats that are common to the region and language your computer is
set to.

If you are not happy with the default date formats that Access
offers you can also type in your own custom date format. To learn
more about creating your own date format, look here .

One other nice feature of the Date/Time data type is the
Show Date Picker property. When enabled(by
selecting “For dates”) Access will show a date picker in the data
sheet when a date cell is selected.

Date picker for date/time data type

Date picker widget for easy date selection

 

 

The
Currency data type

The Currency data type also adapts to the
region and language settings of the operating system. But you can
override this behaviour.

The currency data type is especially suited for calculations
that involve currencies because it prevents rounding errors with
very small fractions that are common to floating point Number types
like Double.

In the Field Properties panel of a Curency field you will find a
Format dropdown box with a number of predefined
formats. Just like with Date/Time values, the formats that show up
here are dependent on your computer’s region and language
settings.

 Currency format

Note that you can also type a custom value into the Format
field. If you live in the US, but want to display your prices in
Polish Zloty’s (the zloty is the currency of Poland) you could
enter a custom currency format like this:

#,###.## zł

This would tell Access to display the zł symbol
behind currency values and to use a thousand separator (the
comma).

 

The AutoNumber data type

If you have worked with databases before you probably know
what AutoNumber is for. AutoNumber is a data type that is used to
let Access generate unique numeric values for you. The AutoNumber
data type is used on primary key fields, because primary keys must
be unique and it is good practice to leave the job of generating
new keys to the database.

When you create a new table in Access , Access automatically
inserts an AutoNumber primary key field called ID for you.

AutoNumber data type

 

The Yes/No
datatype

The Yes/No datatype is called a
boolean data type in most databases. The
Yes/No data type is pretty simple. It just lets you chose between
Yes and No :). This data type is used for yes/no questions. Does
this user want to receive our weekly newsletter? Yes or no? Have we
received payment for this order? Yes or No?

Yes/No fields appear as a checkbox in the data sheet.

yes/no datatype in the data sheet

 

The
Hyperlink datatype

The Hyperlink datatype is a text field, but a
bit fancier. It won’t surprise you that this field is used to store
links. In the data sheet, the stuff you type into a Hyperlink field
is automatically prepended with http:// by Access and it is
displayed as a clickable link.

 

The Attachment datatype

The Attachment datatype is used to store one or
more files in a database field. If you set a field to the
Attachment data type a small paperclip will display in the Access
data sheet. 

Attachment data type in the data sheet

Double clicking the paperclick will open up a window that allows
you to add one or more files from your hard drive.

Attachment selection

The calculated data type

The calculated data type lets you store the result of a
calculation that includes data from other fields, like the amount
of product x the product price. In database design it is considered
bad practice to store information that can be derived from other
fields, because you would be storing the same information in two
locations and you would be forced to manage that information in two
places. 


  • Terms of use

  • Info & contact

  • Dutch site

The content of Tekstenuitleg.net is protected by copyright
© Codebase Software