Text. Essentially the same as the Character type in xBase. 255 character maximum. You can and should define a width, but Access doesn't use fixed width storage so is not as wasteful of disk space.

Number. This category includes Byte (unsigned), Integer (two byte), Long (four byte integer), Single (four byte floating point), and Double (eight byte floating point.)

Currency. An 8-byte fixed point number, designed to give the accuracy needed for financial data. 15 digits of whole dollars, plus four decimal places (hundredths of a cent.)

Counter. Ver 1 and 2. A Long integer type, automatically assigned by Access. In Access 1, counters were assigned when a new record was saved, so if a second user began adding a record before the first user finished adding his, it was possible to get duplicate counters! In Access 2, a counter is reserved when you begin adding a new record, so if you abort, that number is not reused. To look up a Counter in a one-to-many relationship, use a Long for the field type in the related table.

AutoNumber. Ver.7, 8. The AutoNumber replaces the Counter as an automatically assigned number. It can be either a Long integer type, or a ReplicationID (see below), and can be sequential or random. To look up an AutoNumber in a one-to-many relationship, the foreign key must be the same type (Long or ReplicationID.) For a simple database, use a sequential Long AutoNumber. The timing of the assignment is the same as in Access 2 (see Counter above.)

ReplicationID. Ver.7, 8. Use this field type only for a foreign key field that looks up a ReplicationID AutoNumber . The ReplicationID is designed to enable off-line replica databases at different sites to assign primary key values without the likelihood of duplicates begin assigned. Also called a GUID (Globally Unique IDentifier.) If less than 100 new records will normally be added between synchronizations of the replica databases, this type will probably not be needed.

Date/Time. Access stores Dates as 8-byte floating point numbers, where the integer part refers to the day, and the fraction part refers to the time. While Now() + 1/24 is theoretically one hour from now, use the DateAdd() function to avoid rounding errors if you plan to use the result in comparisons. When constructing queries, keep in mind that if a Date/Time field contains a fraction part, it will not match a criteria which is just a date: Now() is not equivalent to Date(), though Int(Now()) is equivalent to Date(.)

Yes/No. A Logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False, equivalent to -1 and 0 respectively. In keeping with true relational theory, a third state - Null - is possible in a Yes/No field.

Memo. Similar to a Memo field in xBase. Up to 64K of text. Memos can not include formatting (e.g. bolding a word.) However, they are searchable

OLE Object. Use this type to store pictures, audio, video, or other BLOBs (Binary Large OBjects.) Double-clicking fires up the associated application. Access 2 and later support in-place activation, e.g. WinWord documents can be stored and edited as OLE objects in a form (though Access will not be able to search them.)

Visual Basic data types are not identical to these field types. In modules, Access supports only the following types:

• boolean (ver. 7, 8: use Integer in ver 1, 2),
• byte (ver. 7, 8: use Integer in ver 1, 2),
• integer,
• long,
• single,
• double,
• currency,
• string,
• object,
• variant,
• date (ver. 7, 8: use Variant in ver 1, 2.)

Variants are very flexible: if the contents look numeric they can treated arithmetically or as strings. For this reason, Access uses ampersand (&) as a concatenation operator. Under certain circumstances, you will need to specifically type-cast variants with CInt(), CStr(), CDate(), etc. This is espcially true of dates in ver. 1 and 2, where CVDate() is needed.

Only variants can contain the values Null, Empty, or Error. In Access a Null is a non-entry and is not the same as a zero-length string.

User-defined types are permitted in all versions, but only in Version 7 and later can user-defined types be based on other user-defined types. Version 7 and 8 also provide powerful string conversions with StrConv(.)

To ensure referential integrity, Access needs to know that only valid EmployeeIDs are allowed in the Supervisor field. This is achieved by dragging two copies of the Employees table into the Relationships screen, and then dragging SupervisorID from one onto EmployeeID in the other. You have just defined a self join.

You will become quite accustomed to working with self-joins if you are asked to develop a report for printing pedigrees. The parents of a horse are themselves horses, and so will have their own records in the table of horses. A SireID field and a DamID field will each refer to different records in the same table. To define these two self-joins requires three copies of the table in the "Relationships" window. Now a full pedigree can be traced within a single table.

Here are the steps to develop the query for the pedigree report:

1. Drag three copies of tblHorses onto a new query. For your own sanity, select tblHorses_1 and change its alias property to Sire in the Properties window. Alias tblHorses_2 as Dam
2. Drag the SireID field from tblHorses to the ID field in Sire. Since we want the family tree even if some entries are missing, this needs to be an outer join, so double-click the line that defines the join and select 2 in the dialog box.
3. Repeat step 2 to create an outer join between DamID in tblHorses and ID in Dam.
4. Now drag four more copies of tblHorses into the query window, and alias them with names like SiresSire, SiresDam, DamsSire, and DamsDam
5. Create outer joins between these four tables, and the appropriate fields in Sire and Dam.
6. Repeat steps 4 and 5 with eight more copies of the table for the next generation.
7. Drag the desired output fields from these tables into the query grid, and your query is ready to view.

And just in case you wish to create this query by copying the SQL, here it is:

• SELECT DISTINCTROW TblHorses.Name, Sire.Name, Dam.Name, SiresSire.Name, SiresDam.Name, DamsSire.Name, DamsDam.Name, SiresSiresSire.Name, SiresSiresDam.Name, SiresDamsSire.Name, SiresDamsDam.Name, DamsSiresSire.Name, DamsSiresDam.Name, DamsDamsSire.Name, DamsDamsDam.Name FROM (((((((((((((TblHorses LEFT JOIN TblHorses AS Sire ON TblHorses.SireID = Sire.ID) LEFT JOIN TblHorses AS Dam ON TblHorses.DamID = Dam.ID) LEFT JOIN TblHorses AS SiresSire ON Sire.SireID = SiresSire.ID) LEFT JOIN TblHorses AS SiresDam ON Sire.DamID = SiresDam.ID) LEFT JOIN TblHorses AS DamsSire ON Dam.SireID = DamsSire.ID) LEFT JOIN TblHorses AS DamsDam ON Dam.DamID = DamsDam.ID) LEFT JOIN TblHorses AS SiresSiresSire ON SiresSire.SireID = SiresSiresSire.ID) LEFT JOIN TblHorses AS SiresSiresDam ON SiresSire.DamID = SiresSiresDam.ID) LEFT JOIN TblHorses AS SiresDamsSire ON SiresDam.SireID = SiresDamsSire.ID) LEFT JOIN TblHorses AS SiresDamsDam ON SiresDam.DamID = SiresDamsDam.ID) LEFT JOIN TblHorses AS DamsSiresSire ON DamsSire.SireID = DamsSiresSire.ID) LEFT JOIN TblHorses AS DamsSiresDam ON DamsSire.DamID = DamsSiresDam.ID) LEFT JOIN TblHorses AS DamsDamsSire ON DamsDam.SireID = DamsDamsSire.ID) LEFT JOIN TblHorses AS DamsDamsDam ON DamsDam.DamID = DamsDamsDam.ID ORDER BY TblHorses.Name WITH OWNERACCESS OPTION;

1. In the data tables, under the dates format enter: mm/dd/yyyy
2. In the format property of the field on its forms enter: mm/dd/yyyy