Notes from the Help Desk…

There are a number of ways to calculate the time between two dates in Access. You can use a calculated field in a query, a calculated control in a form or report, or you can use a VBA procedure to perform the calculation.

If you need to calculate the number of days between two dates, the syntax is:

=[One date field] - [Another date field]

You can use functions to calculate the number of years between two dates:

=Year([One date field]) - Year([Another date field])

You can also use the DateDiff() function, which uses an argument to determine how the time interval is measured.

For example,

=DateDiff("q", Date1, Date2)

returns the number of quarters between the two fields. (In the example above, Date1 would be subtracted from Date2.) The other intervals that can be used in this expression are as follows: “yyyy” – Years, “m” – Months, “y” – Day of Year, “d” – Day, “w” – Weekdays, “ww” – Weeks, “h” – Hours, “n” – Minutes, “s” – Seconds.

Naming Date Fields

When naming date fields in Access, avoid naming a field Date, since this is a built-in Access function. Instead, consider using more descriptive names, such as BeginDate, HireDate, etc.

Hard Coding a Date

When calculating with dates in Access, you can hard-code an arbitrary date by using the following syntax:

#mm/dd/yyyy#

For example, you would use the following code to subtract a date from Jan. 1, 2006:

=#1/1/2006# - [Date Field]

Here are some useful shortcuts for Microsoft Access:

• Undo typing: CTRL+Z or ALT+BACKSPACE
• Undo changes: Press ESC once for current field, ESC twice for current record
• Insert the current date: CTRL+SEMICOLON (;)
• Insert the current time: CTRL+COLON (:)
• Insert the default value for a field: CTRL+ALT+SPACEBAR
• Insert the value from the same field in the previous record: CTRL+APOSTROPHE (’)
• Add a new record: CTRL+PLUS SIGN (+)
• Delete the current record: CTRL+MINUS SIGN (-)
• Recalculate fields: F9

As you work with a database, it can become fragmented so that it uses disk space inefficiently. Compacting the database rearranges the way the database file is stored on the disk so that it takes up less disk space and works more efficiently.

In addition, it’s possible that your database can become damaged or corrupted, although this is not a common occurrence. Typically, a damaged database will behave unpredictably. For example, a search may not display any records, new records may not appear, or deleted records may still show up in a table. In some cases, Access will inform you that your database is damaged when you try to open it.

To compact and repair the current database:

1. Select Tools | Database Utilities | Compact and Repair Database.
2. Access will compact and repair the database.

You can also compact and repair a database that is not open. For this to work, you must not have any database open. Also, when you compact and repair a database that is not open, Access will create a copy of the database and compact and repair the copy. The original will not be compacted and repaired.

To compact and repair a database that is not open:

1. Make sure that no database is open.
2. Select Tools | Database Utilities | Compact and Repair Database.
3. Access will display the following dialog box:



4. Select the database you want to compact. Change drives and/or folders, if necessary.
5. Click the Compact button.
6. Access will display the following dialog box:



7. Type a new name for the database. Change drives and/or folders if necessary.
8. Click the Save button.
9. Access will create a copy of the database, using the name you specified, then compact and repair the new database.

TIP: When Should You Compact a Database?

It’s a good idea to compact your database after you delete information.

Here are some general tips for designing and maintaining databases from one of our experts:

1. When developing forms and reports, focus on functionality first. Once things are working correctly, then you can make them look pretty.
2. When designing tables to hold contact information, try to break information down into its parts.
   Separate name into FN, MI, LN, and Address into Add1, Add2, City, State, Zip, Country.
   It’s easy to put things together afterwards if necessary, not so easy to break them apart.
3. Always back up files (the whole .mdb file, or specific forms, reports, etc.) before making changes to them.
4. On the database menu, Tools>Startup has useful options like setting a form to open automatically, and hiding the database window on open.

When database development is finished, split the database, putting tables in the “back-end” database, and forms, reports, queries, etc in the “front-end”.

Just open the database and go to Tools>Database Utilities>Database Splitter, and follow the instructions.

Splitting improves speed, reliabilty, and convenience, especially in a multi-user environment.