Access 2007[c] The Missing Manual

2017-07-07 02:10:07

2.3. Access Data Types

Design view's a much more powerful place for defining a table than Datasheet view. As you'll see throughout this chapter, Design view allows you to tweak all sorts of details that are hidden in Datasheet view (or just awkward to change).

One of these is the data type of your fielda setting that tells Access what type of information you're planning to store. To change the data type, make a selection in the Data Type column next to the appropriate field (Figure 2-6). Here's where you separate the text from numbers (and other data types). The trick's choosing the best data type from the long list Access providesyou'll get more help in the following section.

Figure 2-6. To choose a data type, click the Data Type column next to the appropriate field. A drop-down list box appears, with 11 choices.

Depending on the data type you choose, there are other field properties that you can adjust to nail down your data type even more precisely. If you use a text data type, then you use field properties to set the maximum length. If you choose a decimal value, then you use field properties to set the number of decimal places. You set field properties in the Field Properties part of the Design view, which appears just under the field list. You'll learn more about field properties throughout this chapter (and you'll consider them again in Chapter 4).

The most important decision you'll make for any field is choosing its data type. The data type tells Access what sort of information you plan to store in that field. Access uses this information to reject values that don't make sense (see Figure 2-7), to perform proper sorting, and to provide other features like calculations, summaries, and filtering.

Figure 2-7. This currency field absolutely does not allow text. Access lets you fix the problem by entering a new value (the right choice) or changing the field data type to text so that it allows anything (the absolutely wrong choice).

Note: A field can have only one data type. You can't create a field that can store two or three different data types, because Access wouldn't have enough information to manage the field properly. (Instead, in this situation, you probably need two separate fields.)

As you learned earlier, there are three basic types of data in the world: text, numbers, and dates. However, Access actually provides a whopping 11 data types, which include many more specialized choices. Before you pick the right data type, it's a good idea to review all your choices. Table 2-1 shows an overview of the first 10 menu options in the Data Type list. (The Lookup wizard choice isn't included, because it isn't a real data type. Instead, this menu option launches the Lookup wizard, which lets you set a list of allowed values. You'll learn more about this in Section 4.4.1 in Chapter 4.)

Table 2-1. Access Data Types

Data Type	Description	Examples
Text	Numbers, letters , punctuation, and symbols, up to a maximum of 255 characters (an average- sized paragraph).	Names , addresses, phone numbers, and product descriptions. This data type's the most common.
Memo	Large amounts of unformatted text, up to 65,536 characters (an average-sized chapter in a novel ).	Articles, memos, letters, arrest warrants , and other short documents.
Number	A variety of different kinds of numbers, including negative numbers and those that have decimal places.	Any type of number except dollar values. Stores measurements, counts, and percentages.
Currency	Similar to Number, but optimized for numbers that represent values of money.	Prices, payments, and expenses.
Date/Time	A calendar date or time of day (or both). Don't use this field for time intervals (the number of minutes in a song, the length of your workout session)instead, use the Number data type.	Birthdates, order dates, ship dates, appointments, and UFO sighting times.
Yes/No	Holds one of two values: Yes or No. (You can also think of this as True or False.)	Fields with exactly two options, like male/ female or approved/unapproved.
Hyperlink	A URL to a Web site, an email address, or a file path .	www.FantasyPets.com, noreplies@ antisocial .co.uk, f:\Documents\Report.doc.
Attachment	One or more separate files. The content from these files is copied into the database.	Pictures, Word documents, Excel spreadsheets, sound files, and so on.
AutoNumber	Stores a number that Access generates when you insert a new record. Every record automatically gets a unique number that identifies it.	Used to uniquely identify each record, especially for a primary key (Section 2.4). Usually, the field's named ID.
OLE Object	Holds embedded binary data, according to the Windows OLE (object linking and embedding) standard. Rarely used, because it leads to database bloat and other problems. The Attachment field's almost always a better choice.	Some types of pictures and documents from other programs. Mostly used in old-school Access databases. Nowadays, database designers use the Attachment data type instead of the OLE Object data type.

The following sections describe each data type except for OLE Object, which is a holdover from the dark ages of Access databases. Each section also describes any important field properties that are unique to that data type.

2.3.1. Text

Text is the all-purpose data type. It accepts any combination of letters, numbers, and other characters. So you can use a text field for a word or two (like "Mary Poppins"), a sentence ("The candidate is an English nanny given to flights of song."), or anything else ("@#$d sf_&!").

WORD TO THE WISE

Changing the Data Type Can Lose Information

The best time to choose the data types for your fields is when you first create the table. That way, your table's completely empty, and you won't run into any problems.

If you add a few records, and then decide to change the data type in one of your fields, life becomes a little more complicated. You can still use Design view to change the data type, but Access needs to go through an extra step and convert the existing data to the new data type.

In many cases, the conversion process goes smoothly. If you have a text field that contains only numbers, you won't have a problem changing the data type from Text to Number. But in other cases, the transition isn't quite so seamless. Here are some examples of the problems you might run into:

You change the data type from Text to Date, but Access can't interpret some of your values as dates.

You change the data type from Text to Number, but some of your records have text values in that field (even though they shouldn't).

You change the data type from Text to Number. However, your field contains fractional numbers (like 4.234), and you forget to change the Field Size property (Section 2.3.3.1). As a result, Access assumes you want to use only whole numbers, and chops off all your decimal places.

The best way to manage these problems is to make a backup (Section 1.3.1) before you make any drastic changes, and be on the lookout for changes that go wrong. In the first two cases in the list above, Access warns you that it needs to remove some values because they don't fit the data type rules (see Figure 2-8). The third problem's a little more insidiousAccess gives you a warning, but it doesn't actually tell you whether or not a problem occurred. If you suspect trouble, switch to Design view, and then check out your data before going any further.

Figure 2-8. Don't say you weren't warned . Here, Access lets you know (in its own slightly obscure way) that it can't make the change you wantmodifying the data type of field from Text to Datewithout throwing out the values in four records. The best course of action is to click No to cancel the change and then take a closer look at your table in Datasheet view to track down the problematic values.

Note: Because text fields are so lax, you can obviously enter numbers, dates, and just about anything else in them. However, you should use text only when you're storing some information that can't be dealt with using another data type, because Access always treats the contents of a text field as plain, ordinary text. In other words, if you store the number 43.99 in a text field, Access doesn't realize you're dealing with numbers, and it doesn't let you use it in a calculation.

Sometimes it seems that the Text data type's just too freewheeling. Fortunately, you can apply some stricter rules that deny certain characters or force text values to match a preset pattern. For example, Access usually treats phone numbers like text, because they represent a series of characters like 123-4444 (not the single number 1,234,444). However, you don't want to let people put letters in a phone number, because they obviously don't belong. To put this restriction into action, you can use input masks (Section 4.2) and validation (Section 4.3), two features discussed in Chapter 4.

2.3.1.1. Text length

Every text field has a maximum length . This trait comes as a great surprise to many people who aren't used to databases. After all, with today's gargantuan hard drives , why worry about space? Can't your database just expand to fit whatever data you want to stuff inside?

The maximum length matters because it determines how densely Access can pack your records together. For performance reasons, Access needs to make sure that an entire record's stored in one spot, so it always reserves the maximum amount of space a record might need. If your table has four fields that are 50 characters apiece, Access can reserve 200 characters worth of space on your hard drive for each record. On the other hand, if your fields have a maximum 100 characters each, Access holds on to twice as much space for each record, even if you aren't actually using that space. The extra space isn't a major issue (you probably have plenty of room on your computer), but the more spread out a database, the slower your searches.

The standard maximum length is 50, a good starting point. The box "Maximum Length Guidelines" (Section 2.3.2.1) has some more recommendations.

To set the maximum length, enter a number in the Field Size box, in the Field Properties section (Figure 2-9). The largest maximum you're allowed is 255 characters. If you need to store a large paragraph or an entire article's worth of information, then you need the Memo data type instead (Section 2.3.1.1).

Tip: It's worthwhile being a little generous with maximum lengths to avoid the need to modify the database later on.

2.3.2. Memo

Microsoft designed the Memo data type to store large quantities of text. If you want to place a chapter from a book, an entire newspaper article, or just several paragraphs into a field, you need the Memo data type. The name 's a little oddalthough a memo field could certainly store the information from an inter-office memorandum, it's just as useful any time you have large blocks of text.

When creating a memo field, you don't need to supply a maximum length, because Access stores the data in a memo field differently from other data types. Essentially , it stuffs memo data into a separate section, so it can keep the rest of the record as compact and efficient as possible, but accommodate large amounts of text.

Figure 2-9. To set a maximum length, choose your field, and then click the Field Size box in the Field Properties list (shown here). (All the field properties you need in this chapter are on the General tab.) When you click a field property box, that field property's description appears on the right.

A memo field tops out at 65,536 characters. To put it in perspective, that's about the same size as this chapter. If you need more space, then add more than one memo field.

Note: Technically, the 65,536 character limitation's a limitation in the Access user interface, not the database. If you program an application that uses your database, it could store far moreup to a gigabyte's worth of information in a memo field.

If you need to edit a large amount of text while you're working on the datasheet, then you can use the Zoom box (Figure 2-10). Just move to the field you want to edit, and then press Shift+F2.

2.3.2.1. Formatted text

Like a text field, the memo field stores unformatted text. However, you can also store rich text in a memo fieldtext that has different fonts, colors, text alignment, and so on. To do so, set the Text Format setting to Rich Text (rather than Plain Text).

To format part of your text, you simply need to select it and then choose a formatting option from the ribbon's Home Font Home Rich Text sections. However, most of the time you wont take this approach, because it's difficult to edit large amounts of text in the datasheet's narrow columns . Instead, use Shift+F2 to open a Zoom box, and then use the minibar (Figure 2-11).

Figure 2-10. If you have lengthy text in a field, it's hard to see it all at once without a lot of scrolling. By opening a Zoom box (Shift+F2), you can see more content and edit it more easily. You'll need to click OK (to accept your edits) or Cancel (to abandon them) to get back to the datasheet.

UP TO SPEED

Maximum Length Guidelines

Here are some recommended maximum lengths:

First names and last names . 25 characters handles a first name, while 50 characters each plays it safe for a long, hyphenated last name.

Middle initial . One character. (Sometimes common sense is right.)

Email address . Go with 50 characters. Email addresses closer to 100 characters have turned up in the wild (Google "world's longest email address" for more), but they're unlikely to reach your database.

Cities, states, countries , and other places . Although a Maori name for a hill in New Zealand tops out at over 80 characters (see http://en.wikipedia.org/wiki/Longest_word_in_English), 50 is enough for most practical purposes.

Street address . A street address consists of a number, followed by a space, then the street name, another space, and the street abbreviation (like Rd or St). Fifty characters handles it, as long as you put postal codes, cities, and other postal details in other fields.

Phone numbers, postal codes, credit card numbers, and other fixed-length text . Count the number characters and ignore the placeholders, and set the maximum to match. If you want to store the phone number (123) 456-7890, make the field 10 characters long. You can then store the phone number as 1234567890, but use an input mask (Section 4.2) to add the parentheses, spaces, and dash when you display it. This approach is better because it avoids the headaches that result from entering similar phone numbers in different ways.

Description or comments . 255 characters fits three or four average sentences of information. If you need more, consider the memo data type instead (Section 2.3.1.1).

Figure 2-11. To show the minibar (sadly, of the non-alcoholic variety), select some text, and then hover over it with the mouse. The minibara compact toolbar with formatting optionsgradually fades into view. The minibar's sometimes a little finicky , and you may need to reselect the text more than once to get it to appear.

Tip: There's another, even easier way to get formatted text into a memo field. Create the text in a word processing program (like Word), format it there, and then copy and paste it into the field. All the formatting comes with it.

As neat as this feature may seem at first glance, it's rarely worth the trouble. Database purists believe that tables should store raw information and let other programs (or fancy forms) decide how to format it. The problem is that once you've created your formatted text, it can be quite a chore to maintain it. Just imagine having to change the font in 30,000 different records.

If you really do want to store formatted content, then consider linking your database to a separate document, like a Word file. In Access, you can do this in two ways:

Create a field that points to the file . For example, c:\myfile\BonoBobblehead-Description.docx). For this trick, use the Text or Hyperlink data type (Section 2.3.7).

Embed the file inside your database . This way, it's impossible to lose the file (or end up pointing to the wrong location). However, you'll need to pull the file out every time you want to update it. To do this, you need to use the Attachment data type (Section 2.3.8).

2.3.3. Number

The Number data type includes a wide variety of differently sized numbers. You can choose to allow decimal numbers, and you can use negative values (just precede the value with a minus sign). You should use the Number data type for every type of numeric information you haveexcept currency amounts, in which case the Currency data type (Section 2.3.4) is a better match.

When you use numeric fields, you don't include information about the units you're using. You may have a field that represents a Weight in pounds , a Height in Meters, or an Age in Years . However, these fields contain only a number. It's up to you to know what that number signifies. If you think other people may be confused , consider explaining the units in the description (Section 2.2.1), or incorporate it into the field name (like HeightInMeters).

Note: Your field should never, ever contain values like "44 pounds." Access treats this value as a text value, so if you make this mistake, you can't use all the important number crunching and validation tools you'll learn about later in this book.

2.3.3.1. Number size

As with a text field, when you create a number field, you need to set the Field Size property to make sure Access reserves the right amount of space. However, with numbers your options are a little more complicated than they are for ordinary text.

Essentially, numbers are divided into several subgroups, depending on whether or not they support fractional values (numbers to the right of a decimal point) and how many bytes of space Access uses to store them.

Note: A byte's a group of eight bits, which is the smallest unit of storage in the computer world. For example, a megabyte's approximately one million bytes.

Table 2-2 lists the different Field Size options you can choose for the Number data type, and explains when each one makes most sense. Initially, Access chooses Long Integer for all fields, which gives a fair bit of space but doesn't allow fractional values.

Table 2-2. Field Size Options for the Number Data Type

Field Size	Contains	When to Use It
Byte	An integer (whole number) from 0 to 255. Requires just one byte of space.	This size is risky, because it fits only very small numbers. Usually, it's safer to use Integer for small numbers and give yourself a little more breathing room.
Integer	An integer (whole number) from32,768 to 32,767. Requires two bytes of space.	Useful if you need small numbers with no decimal part.
Long Integer	An integer (whole number) from2,147,483,648 to 2,147,483,647. Requires four bytes of space.	The Access standard. A good choice with plenty of room. Use this to store just about anything without hitting the maximum, as long as you don't need decimals.
Single	Positive or negative numbers with up to 38 zeroes and 7 decimal places of accuracy. Requires four bytes of space.	The best choice if you need to store fractional numbers or numbers that are too large to fit in a Long Integer.
Double	Positive or negative numbers with up to 308 zeroes and 15 decimal places of accuracy. Requires eight bytes of space.	Useful if you need ridiculously big numbers.
Decimal	Positive or negative numbers with up to 28 zeroes and 28 decimal places of accuracy. Requires eight bytes of space.	Useful for fractional numbers that have lots of digits to the right of the decimal point.

Note: Table 2-2 doesn't include Replication ID, because you use that option only with the Number data type (Section 2.3.9).

2.3.3.2. Number formatting

The Field Size determines how Access stores your number in the table. However, you can still choose how it's presented in the datasheet. For example, 50, 50.00, 5E1, $50.00, and 5000% are all the same number behind the scenes, but people interpret them in dramatically different ways.

To choose a format, you set the Format field property. Your basic built-in choices include:

General Number . Displays unadorned numbers, like 43.4534. Any extra zeroes at the end of a number are chopped off (so 4.10 becomes 4.1).

Currency and Euro . Both options display numbers with two decimal places, thousands separators (the comma in $1,000.00), and a currency symbol. These choices are used only with the Currency data type (Section 2.3.4).

Fixed . Displays numbers with the same number of decimal places, filling in zeroes if necessary (like 432.11 and 39.00). A long column of numbers lines up on the decimal point, which makes your tables easier to read.

Standard . Similar to Fixed, except it also uses thousands separators to help you quickly interpret large numbers like 1,000,000.00.

Percent . Displays fractional numbers as percentages. For example, if you enter 0.5, that translates to 50%.

Scientific . Displays numbers using scientific notation, which is ideal when you need to handle numbers that range widely in size (like 0.0003 and 300). Scientific notation displays the first non-zero digit of a number, followed by a fixed number of digits, and then indicates what power of ten that number needs to be multiplied by to generate the specified number. For example, 0.0003 becomes 3.00 x 10 ^-4 , which displays as 3.00E-4. The number 300, on the other hand, becomes 3.00 x 10 ² , or 3E2.

Tip: When using Fixed, Standard, Percent, or Scientific, you should also set the Decimal Places field property to the number of decimal places you want to see. Otherwise, you always get two.

A custom format string . This is a cryptic code that tells Access exactly how to format a number. You need to type the format string you need into the Format box. For example, if you type in the weird-looking code #,##0, (including the comma at the end) Access hides the last three digits of every number, so 1 million appears as 1,000 and 15,000 as 15.

Note: Custom number formats aren't terribly common in Access (they're more frequently used with Excel). Later on, you'll learn about expressions (Section 7.1.1), which let you do pretty much the same thing.

2.3.4. Currency

Currency's a slight variation on the Number data type that's tailored for financial calculations. Unlike the Number data type, here you can't choose a Field Size for the Currency data typeAccess has a one-size-fits-all policy that requires eight bytes of storage space.

Note: The Currency data type's better than the Number data type because it uses optimizations that prevent rounding errors with very small fractions. The Currency data type's accurate to 15 digits to the left of the decimal point, and four digits to the right.

You can adjust the number of decimal places Access shows for currency values on the datasheet by setting the Decimal Places field property. Usually, it's set to 2.

The formatting that Access uses to display currency values is determined by the Regional and Language Options settings on your computer (Section 2.3.5). However, these settings might produce results you don't wantfor example, say you run an artisanal cereal business in Denmark that sells all its products overseas in U.S. dollars (not kroner). You can control exactly how currency values are formatted by setting the Format field property, which gives you the following options:

Currency . This option is the standard choice. It uses the formatting based on your computer's regional settings.

Euro . This option always uses the Euro currency symbol ( )

A custom format string . This option lets you get any currency symbol you want (as described below). You need to type the format string you need into the Format box.

There's a simple recipe for cooking up format strings with a custom currency symbol. Start by adding the character for the currency symbol (type in whatever you want) and then add #,###.## which is Access code for "give me a number with thousands separators and two decimal places."

For example, the Danish cereal company could use a format string like this to show the U.S. currency symbol:

$#,###.##

Whereas a U.S. company that needs to display a Danish currency field (which formats prices like kr 342.99 ) would use this:

kr #,###.##

Note: Enterprising users can fiddle around with the number format to add extra text, change the number of decimal places (just add or remove the number signs), and remove the thousands separators (just take out the comma).

2.3.5. Date/Time

Access uses the Date/Time data type to store a single instant in time, complete with the year, month, day, and time down to the second. Behind the scenes, Access stores dates as numbers, which lets you use them in calculations.

Although Access always uses the same amount of space to store date information in a field, you can hide some components of it. You can choose to display just a date (and ignore any time information) or just the time (and ignore any date information). To do this, you simply need to set the Format field property. Table 2-3 shows your options.

Table 2-3. Date/Time Formats

Format	Example
General Date	2/23/2008 11:30:15 PM
Long Date	February 23, 2008 11:30:15 PM
Medium Date	23-Feb-08
Short Date	2/23/2008
Long Time	11:30:15 PM
Medium Time	11:30 PM
Short Time	23:30

Note: Both the General Date and Long Date show the time information only if it's not zero.

The format affects only how the date information's displayedit doesn't change how you type it in. Access is intelligent enough to interpret dates correctly when you type any of the following:

2008-23-2 (the international year-month-day standard always works)

2/23/2008 (the most common approach, but you might need to flip the month and day on non-U.S. computers)

23-Feb-08

Feb 23 (Access assumes the current year)

23 Feb (ditto)

To add date and time information, just follow the date with the time, as in 23-Feb-08 5:06 PM. Make sure to include the AM/PM designation at the end, or use a 24hour clock.

If it's too much trouble to type in a date, then consider using the calendar smart tag instead. The smart tag is an icon that appears next to the field whenever you move to it, as shown in Figure 2-12.

Figure 2-12. Access automatically pops up the calendar smart tag for all date fields. Click the calendar icon to pop up a mini calendar where you can browse to the date you want. However, you can't use the calendar to enter time information.

UP TO SPEED

Dating Your Computer

Windows has regional settings for your computer, which affect the way Microsoft programs display things like dates and currencies. In Access, the regional settings determine how the different date formats appear. In other words, on a factory-direct U.S. computer, the Short Date format shows up as 2/23/2008. But on a British computer, it may appear as 23/2/2008. Either way, the information that's stored in the database is the same. However, the way it appears in your datasheet changes.

You can change the regional settings, and they don't have to correspond to where you liveyou can set them for your company headquarters on another continent , for instance. But keep in mind that these settings are global, so if you alter them, you affect all your programs.

To make a switch, head to Control Panel. (In Windows XP, click the Start menu and choose Settings Control Panel. In Windows Vista, click Start and look for the Control Panel option on the right side.) Once youve opened the Control Panel, double-click Regional and Language Options, which brings up a dialog box. The first tab has all the settings you want. The most important setting's in the first box, which has a drop-down list you can use to pick the region you want to use, like English (United States) or Swedish (Finland).

You can fine-tune the settings in your region, too. This makes sense only if you have particular preferences about how dates should be formatted that don't match the standard options. Click the Customize button next to the region box to bring up a new dialog box, and then click the Date tab (shown in Figure 2-13).

Figure 2-13. The Regional and Language Options dialog box lets you customize how dates appear on your computer. Use the drop-down lists to specify the date separator; order of month, day, and year components in a date; and how Access should interpret two-digit years. You can mix and match these settings freely , although you could wind up with a computer that's completely counterintuitive to other people.

2.3.5.1. Custom date formats

If you're not happy with the seven standard date options that Access provides, you can craft your own date format string and type in the Format property. This format string tells Access how to present the date and time information.

A date format string is built out of pieces. Each piece represents a single part of the date, like the day, month, year, minute, hour , and so on. You can combine these pieces in whatever order you want. For example, consider the following format string:

yyyy-mm-dd

This string translates as the following instructions: Display the four-digit year, followed by a dash, followed by a two-digit month number, followed by another dash, followed by a two-digit day number. You're free to put these components in any order you like, but this example defines them according to the ISO date standard. You can also control how to display the year, day, and month components. You can use month abbreviations or full names instead of a month number (just replace the mm code with something different).

If you apply this format string to a field that contains the date January 1, 2008, then you see this in the datasheet:

2008-01-01

Remember that regardless of what information you choose to display or hide, Access stores the same date information in your database.

Table 2-4 shows the basic placeholders that you can use for a date or time format string.

Table 2-4. Date and Time Formatting Codes

Code	Description	Displays (for the Date January 1, 2008)
d	The day of the month, from 1 to 31, with the numbers between 1 and 9 appearing without a leading 0.	7
dd	The day of the month, from 01 to 31 (leading 0 included for 1 to 9).	07
ddd	A three-letter abbreviation for the day of the week.	Fri
dddd	The full name of the day of the week.	Friday
m	The number value, from 1 to 12, of the month (no leading 0 used).	1
mm	The number value, from 01 to 12, of the month (leading 0 used for 01 to 09).	01
mmm	A three-letter abbreviation for the month.	Jan
mmmm	The full name of the month.	January
yy	A two-digit abbreviation of the year.	08
yyyy	The year with all four digits.	2008
h	The hour, from 0 to 23 (no leading 0 used).	13
hh	The hour, from 00 to 23 (leading 0 used for 00 to 09).	13
:m	The minute, from 0 to 59 (no leading 0 used).	5
:mm	The minute, from 0 to 59 (leading 0 used for 00 to 09).	05
:s	The second, from 0 to 59 (no leading 0 used).	5
:ss	The second, from 0 to 59 (leading 0 used for 00 to 09).	05
AM/PM	Tells Access to use a 12-hour clock, with an AM or PM indication.	PM
am/pm	Indicates a 12-hour clock, with an am or pm indication.	pm
A/P	Tells Access to use a 12-hour clock, with an A or P indication.	p
a/p	Tells Access to use a 12-hour clock, with an a or p indication.	p

2.3.6. Yes/No

A Yes/No field is a small miracle of efficiency. It's the leanest of Access data types, because it allows only two possible values: Yes or No.

When using a Yes/No field, imagine that your field poses a yes or no question by adding an imaginary question mark at the end of your field name. You could use a field named InStock to keep track of whether or not a product's in stock. In this case, the yes or no question is "in stock?" Other examples include Shipped (in a list of orders), Male (to separate the boys from the girls ), and Republican ( assuming you're willing to distinguish between only two political orientations).

Although every Yes/No field is essentially the same, you can choose to format it slightly differently, replacing the words "Yes" and "No" with On/Off or True/False. You'll find these three options in the Format menu. However, it doesn't make much difference because on the datasheet, Yes/No fields are displayed with a checkbox, as shown in Figure 2-14.

Figure 2-14. In this example, ForSale is a Yes/No field. A checked checkbox represents Yes (or True or On). An unchecked checkbox represents No (or False or Off).

2.3.7. Hyperlink

The Hyperlink data type comes in handy if you want to create a clickable link to a Web page, file, or email address. You can mix and match any combination of the three in the same table.

Access handles hyperlinks a little differently in the Datasheet view. When you type text into a hyperlink field, it's colored blue and underlined . And when you click the link, Access pops it open in your browser (Figure 2-15).

Note: Access doesn't prevent you from entering values that aren't hyperlinks in a hyperlink data field. This trait leads to problems if you click the hyperlink. If you put the text "saggy balloons" in a hyperlink field and click it, then Access tries to send your browser to http://saggy balloons, which obviously doesn't work.

Figure 2-15. Click this hyperlink, and you'll head straight to the welcoming arms of Office Online.

One hyperlink field feature isn't immediately obvious. Hyperlink fields actually store more than one piece of information. Every hyperlink includes these three components:

The text you see in the cell

The destination you go to when you click the cell (the URL or file path)

The text you see when you hover over the link with your mouse (the tooltip)

When you type a link into the datasheet, all three of these are set to the same valuewhatever you've just typed in. In other words, when you type http://www.FantasyPharmacologists.com, the text you see, the URL link, and the tooltip are all set to hold the same content, which is the URL http://www.FantasyPharmacologists.com.

Most of the time, this approach is good, because it lets you quickly size up a link. However, you aren't limited to this strategy. If you want to set these three components to have different values, move to the value, and then hit Ctrl+K to pop up the Edit Hyperlink window (see Figure 2-16). Or right-click it, and then choose Hyperlink Edit Hyperlink.

2.3.8. Attachment

The Attachment data type's new in Access 2007. It lets you add files to your database record in much the same way that you tack on attachments to your email messages. Access stores the files you add to an attachment field as part of your table, embedded inside your database file.

The Attachment data type's a good choice if you need to insert a picture for a record, a short sound file, or even a document from another Office application, like Word or Excel. You could create a People table with a picture of each person in your contact list, or a product catalog with pictures of the wares you're selling. In these cases, attachments have an obvious benefitbecause they're stored inside your database file, you'll never lose track of them.

Figure 2-16. Using the Edit Hyperlink window, you can change the text that appears in the cell (at the top of the window) and the page that Access opens when you click it (at the bottom). You can also create links that use email addresses (in which case Access opens the email program that's configured on your computer) or links to file paths (use the folder browsing area to pick the file you want).

However, attachments aren't as graceful with large files, or files you need to modify frequently. If you place a frequently modified document into an Access database, it isn't available on your hard drive for quick editing, printing, and searching. Instead, you'll need to fire up Access, and then find the corresponding record before you can open your document. If you want to make changes, then you'll also need to keep Access open so it can take the revised file and insert it back into the database.

Warning: Think twice before you go wild with attachments. As you've already learned, an Access database is limited to two gigabytes of space. If you start storing large files in your tables, you just may run out of room. Instead, store large documents in separate files, and then record the file name in a text or hyperlink field.

When you use the Attachment data type, make sure you set the Caption field property, which determines the text that appears in the column header for that field. (Often, you'll use the field name as the caption.) If you don't set a caption, the column header shows a paper clip but no text.

You'll recognize an attachment field in the datasheet because it has a paper clip icon next to it (Figure 2-17).

To attach a file or review the list of attached files, double-click the paper clip icon. You'll see the Attachments dialog box (see Figure 2-18).

Here's what you can do in the Attachments window:

Add a new attachment . Click the Add button. Then browse to a new file, and then click OK. You'll see it appear at the bottom of the list.

Delete an attachment . Select the attachment in the list, and then click Remove.

Figure 2-17. Attachments are flagged with a paper clip icon and a number in brackets, which tells you how many files are attached. In this example, all the values in the Picture attachment field are empty except Count Chocula, which has two.

Figure 2-18. The Attachments dialog box shows you all the files that are linked to your field.

Save a copy of an attachment . Select the attachment, click Save, and then browse to a location on your computer. Or, click Save All to save copies of all the attachments in this field. If you change these copies, you don't change the attachment in the database.

Edit or view an attachment . Select the attachment, and then click Open. Access copies the attachment to a temporary folder on your computer, where Internet content is cached. If you save the file, then Access notices the change, updates the attachment automatically, and then removes the file. If you close the Attachments window before you've closed the file, then Access warns you that your updates might not be reflected in the database. Figure 2-19 shows what happens.

Unfortunately, the Attachment data type doesn't give you a lot of control. Here are some of its limitations:

You can't restrict the number of attachments allowed in an attachment field. All attachment fields allow a practically unlimited number of attachments (although you can't attach two files with the same name).

You also can't restrict the types of files used for an attachment.

You can't restrict the size of the files used for an attachment.

Figure 2-19. Top: In this example, the file "The Story of the Count. doc" is still open. If you continue, then any changes you make (or any changes you've made so far and haven't saved) aren't reflected in the database.

Bottom: If Access notices you've saved your file since you first opened it, then Access also asks if you want to update the database with the last saved version. (To avoid such headaches, attach only files that you don't plan to edit.)

2.3.9. AutoNumber

An AutoNumber is a special sort of data type. Unlike all the other data types you've seen, you can't fill in the value for an AutoNumber field. Instead, Access does it automatically whenever you insert a new record. Access makes sure that the AutoNumber value is uniquein other words, it never gives two records the same AutoNumber value.

Note: Every table can have up to one AutoNumber field.

Ordinarily, the AutoNumber field looks like a sequence of numbersAccess tends to give the first record an AutoNumber value of 1, the second an AutoNumber of 2, and so on. However, the truth isn't so straightforward. Sometimes, Access skips a number. This skipping could happen when several people are using a database at once, or if you start adding a new record, and then cancel your action by pressing the Esc key. You may also delete an existing record, in which case Access never reuses that AutoNumber value. As a result, if you insert a new record and you see it's assigned an AutoNumber value of 401, then you can't safely assume that there are already 400 records in the table. The actual number's probably less.

Truthfully, an AutoNumber value doesn't represent anything, and you probably won't spend much time looking at it. The AutoNumber field's sole purpose is to make sure you have a unique way to point to each record in your table. Usually, your AutoNumber field's also the primary key for your table, as explained in Section 2.4.

2.3.9.1. Using AutoNumbers without revealing the size of your table

AutoNumber values have one minor problem: they give a clue about the number of records in a table. You may not want a customer to know that your brand-new food and crafts company, Better Butter Sculptures, hasn't cracked 12 customers. So you'll be a little embarrassed to tell him he's customer ID number 6.

The best way to solve this problem is to start counting at a higher number. You can fool Access into generating AutoNumber values starting at a specific minimum. For example, instead of creating customer IDs 1, 2, and 3, you could create the ID values 11001, 11002, 11003. This approach also has the advantage of keeping your IDs a consistent number of digits, and it allows you to distinguish between IDs in different tables by starting them at different minimums. Unfortunately, in order to pull this trick off, you need to fake Access out with a specially designed query, which you'll see in Section 8.3.2.

Alternatively you can tell Access to generate AutoNumber values in a different way. You have two choices:

Random AutoNumber value . To use random numbers, change the New Values field property from Increment to Random. Now you'll get long numbers for each record, like 212125691, 1671255778, and1388883525. You might use random AutoNumber to create values that other people can't guess. (For example, if you have an Orders table that uses random values for the OrderID field, you can use those values as confirmation numbers.) However, random Auto-Numbers are rarely used in the Access world.

Replication IDs . Replication IDs are long, obscure codes like 38A94E7B-2F95-4E7D-8AF1-DB5B35F9700C that are statistically guaranteed to be unique. To use them, change the Field Size property from Long Integer to Replication ID. Replication IDs are really used only in one scenarioif you have separate copies of a database and you need to merge the data together in the future. The next section explains that scenario.

Both of these options trade the easy-to-understand simplicity of the ordinary AutoNumber with something a little more awkward, so evaluate them carefully before using these approaches in your tables.

2.3.9.2. Using replication IDs

Imagine you're working at a company with several regional sales offices, each with its own database for tracking customers. If you use an ordinary AutoNumber field, then you'll end up with several customers with the same ID, but at different offices. If you ever want to compare data, you'll quickly become confused. And you can't combine all the data into one database for further analysis later on.

Access gives you another choicea replication ID . A replication ID's a strange creationit's an extremely large number (16 bytes in all) that's represented as a string of numbers and letters that looks like this:

38A94E7B-2F95-4E7D-8AF1-DB5B35F9700C

This ID's obviously more cumbersome than an ordinary integer. After all, it's much easier to thank someone for submitting Order 4657 than Order 38A94E7B-2F95-4E7D-8AF1-DB5B35F9700C. In other words, if you use the AutoNumber value for tracking or bookkeeping, then the replication ID's a bad idea.

However, the replication ID solves the problem described earlier, where multiple copies of the same database are being used in different places. That's because replication IDs are guaranteed to be statistically unique . In other words, there are so many possible replication IDs that it's absurdly unlikely that you'll ever generate the same replication ID twice. So even if you have dozens of separate copies of your database, and they're all managing hundreds of customers, you can rest assured that each customer has a unique customer ID. Even better, you can periodically fuse the separate tables together into one master database. (This process is called replication , and it's the origin of the term replication ID. You'll learn more about transferring data from one database to another in Chapter 19.)

Note: A replication ID is also called a GUID (short for "globally unique identifier"). In theory, the chance of two GUIDs being identical are one in 2 ¹²⁸ , which is small enough that you could set one billion people to work, ask them to create one billion GUIDs a year, and still be duplicate-free for the next decade or two. In practice, the real limitation's how good the random number generator is in Access.

Figure 2-20 shows a table that uses replication IDs.

Figure 2-20. This figure shows 10 records in the FictionalCharacters table, each with a statistically unique AutoNumber value.