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.
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.
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
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.
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_&!").
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.
18.104.22.168. 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 22.214.171.124) 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 126.96.36.199).
Tip: It's worthwhile being a little generous with maximum lengths to avoid the need to modify the database later on.
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.
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.
188.8.131.52. 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).
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:
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.
184.108.40.206. 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
Note: Table 2-2 doesn't include Replication ID, because you use that option only with the Number data type (Section 2.3.9).
220.127.116.11. 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:
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.
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.
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:
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:
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).
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
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:
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.
18.104.22.168. 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:
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:
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
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.
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.
One hyperlink field feature isn't immediately obvious. Hyperlink fields actually store more than one piece of information. Every hyperlink includes these three components:
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.
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.
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:
Unfortunately, the Attachment data type doesn't give you a lot of control. Here are some of its limitations:
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.
22.214.171.124. 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:
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.
126.96.36.199. 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:
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 128 , 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.