Focusing on Fields in Access 2003 - dummies

Focusing on Fields in Access 2003

By John Kaufeld

A field is the place where your data lives; one field holds one piece of data, such as Year or Team.

Because there are so many different kinds of stuff in the world, Access 2003 offers a variety of field types for stuff storage. In fact, Access 2003 puts ten field types at your disposal. At first blush, ten choices may not seem like much flexibility, but once you really get into Access 2003, it’ll be more than enough. And, thanks to the field options, you also can customize how the fields look and work to suit your needs precisely. All this, and it makes popcorn, too.


Each field offers a number of options to make customizing incredibly useful. You can ask for some information, test the entry to see whether it’s what you’re looking for, and then automatically format the field just the way you want.

All the field types appear in the following list. They’re in the same order as they appear on-screen in Access 2003. Don’t worry if you can’t figure out why anyone would want to use one type or another. Just focus on the ones you need, make a mental note about the others, and go on with your work.

  • Text: Stores text — letters, numbers, and any combination thereof — up to 255 characters.

    Numbers in a text field aren’t numbers; they’re just a bunch of digits hanging out together in a field. Be careful of this fact when you design the tables in your database.

    Text fields have one setting you need to know about: size. When you create a text field, Access 2003 wants to know how many characters the field holds. That’s the field size. If you create a field called First Name and make its size 6, Joseph fits into the field, but not Jennifer. This restriction can be a problem. A good general rule is to make the field a little larger than you think you need. It’s easy to make the field even larger if you need to, but it’s very dangerous to make it smaller.

  • Memo: Holds up to 64,000 characters of information — that’s almost 18 pages of text.

    This is a really big text field. It’s great for general notes, detailed descriptions, and anything else that requires a lot of space.

  • Number: Holds real, for-sure numbers.

    You can add, subtract, and calculate your way to fame and fortune with number fields. If you’re working with dollars and cents (or pounds and pence), use a currency field.

  • Date/Time: Stores time, date, or a combination of the two, depending on which format you use.

    Use a Date/Time field to track the whens of life. Pretty versatile, eh?

  • Currency: Tracks money, prices, invoice amounts, and so on.

    In an Access 2003 database, the bucks stop here. For that matter, so do the lira, marks, and yen. If you’re in the mood for some other kind of number, check out the number field.

  • AutoNumber: Does just what it says: It fills itself with an automatically generated number every time you make a new record.

    AutoNumber is very cool. Just think, when you add a customer to your table, Access 2003 generates the customer number automatically! Although Microsoft SQL Server has something similar, the poor people who use Oracle have to jump through hoops to generate customer (or other types of) numbers.

  • Yes/No: Holds Yes/No, True/False, and On/Off, depending on the format you choose. When you need a simple yes or no, this is the field to use.
  • OLE object: Stands for Object Linking and Embedding, a very powerful, very nerdy technology, and is pronounced “O-Lay.” An OLE object is something like a Word document, an Excel spreadsheet, a Windows bitmap (a picture), or even a MIDI song. By embedding an OLE object in your table, your database will automatically “know” how to edit a Word document or an Excel spreadsheet, play a MIDI song, and so on.
  • Hyperlink: Thanks to this field type (and a little bit of Net magic provided by Microsoft Internet Explorer), Access 2003 now understands and stores the special link language that makes the Internet such a cool place.

    If you use Access 2003 on your company’s network or use the Internet extensively, this field type is for you.

  • Lookup Wizard: One of a database program’s most powerful features is the lookup. It makes data entry go faster (and with fewer errors) by letting you pick a field’s correct value from a preset list. No typing, no worry, no problem — it’s quite a helpful trick. In some database programs, adding a lookup to a table is really hard. Luckily, the Access 2003 Lookup Wizard makes the process much less painful. Ask the Office Assistant for all the details about the Lookup Wizard.