Selecting Multiple Fields (All Versions)

Posted April 26, 2011 By Mike

A quick way to place several successive fields on a form or report is to click the first field you want in the field list, scroll down until you see the last field you want, and then hold down the Shift key while you click the last field. This procedure selects all the fields between the first and last fields you selected. You can also double-click the title bar of the field list to select all the fields in the field list. Holding down the Ctrl key and clicking several non-contiguous fields works too. Click any of the highlighted fields and drag the fields as a group to the Detail section of the form or report.

Sizing a Label (All Versions)

Posted April 26, 2011 By Mike

To quickly adjust the size of a label, select the label, choose the Size command from the Format menu, and then choose To Fit from the submenu. You can also
double-click any of the sizing handles to perform a size to fit.

Selecting Controls in a Row (All Versions)

Posted April 26, 2011 By Mike

To select all controls in a vertical area, click the horizontal ruler above the area containing the controls you want to select. Likewise, to select all controls in a horizontal area, click the vertical ruler.

An easy way to select all the fields in a table is to double-click the title bar of the field list in the upper part of the Query design window. This highlights all the fields. Then click any of the highlighted fields and drag them as a group to the Field row in the design grid. While you’re dragging, the mouse pointer changes to a multiple rectangle icon, indicating that you’re dragging multiple fields. When you release the mouse button, you’ll see that Access has copied all the fields to the design grid for you.

AND vs. OR (All versions)

Posted April 26, 2011 By Mike

It’s a common mistake to get OR and AND mixed up when typing a compound criteria for a single field. You may think to yourself, “I want all the entertainment groups in the states of Washington and California,” and then type: WA AND CA in the Criteria row for the StateOrProvince field. When you do this, you’re asking Access to find rows where (StateOrProvince = “WA”) AND (StateOrProvince = “CA”). Since a field in a record can’t have more than one value at a time (it can’t contain both the values “WA” and “CA” in the same record), there won’t be any records in the output. To look for all the rows for these two states, you need to ask Access to search for (StateOrProvince = “WA”) OR (StateOrProvince= “CA”). In other words, type WA OR CA in the Criteria row under the StateOrProvince field.

You can right-click any table in the Relationships window and then choose Table Design from the shortcut menu to open that table in Design view. You can also choose Print Relationships (Access 2000) from the File menu while viewing the Relationships window to create a report that prints what you have laid out in the window.

Copy Toolbar Menu

Posted April 26, 2011 By Mike

When you copy a built-in menu (either from an available menu bar or from the Built-In Menus category), Access does not make an independent copy of the original. So, if you copy a menu and then change some of its properties (for example, you delete one of the commands from the menu), you’re also affecting the built-in menu. This is why you must use the New Menu command for the first menu level (File, Edit, View, etc.) on a custom menu bar. If you were to copy the originals and delete unwanted commands from the copies, you’d be deleting them from the built-in menus as well.

Toolbar Context

Posted April 26, 2011 By Mike

If you open one of the built-in toolbars in a context in which the toolbar would not normally be open, the toolbar remains open until you close it. For example, if you open the Customize dialog box while the focus is on the Database window and then open the Form Design toolbar, the toolbar remains open no matter what you are doing in Access. Likewise, if you close a toolbar in a context in which that toolbar is normally open (for example, if you close the Formatting toolbar in a Form window in Design view), that toolbar will remain closed until you open it again within the usual context or from the Customize dialog box.

Locking a Tool (Access 97 and later)

Posted April 22, 2011 By Mike

When you select a tool other than the Select Objects tool, that tool becomes deselected after you use it to place a control on your form. If you plan to create several controls using the same tool—for example, a series of check boxes in an option group—double-click the control button in the toolbox to “lock” it. You can unlock it by clicking any other tool button (including the Select Objects tool).

Updatability (V2 and later)

Posted April 22, 2011 By Mike

The recordset that Microsoft Access creates when you run a query looks and acts pretty much like a real table containing data. In fact, in most cases you can insert rows, delete rows, and update the information in a recordset, and Access will make the necessary changes to the underlying table or tables for you.

In some cases, however, Access won’t be able to figure out what needs to be changed. Consider, for example, any calculated field. If you try to increase the amount in a Total field whose value is a result of multiplying data in the Quantity field by data in the Price field, Access can’t know whether you mean to update the Quantity field or the Price field. On the other hand, you can change either the Price field or the Quantity field and then immediately see the change reflected in the calculated Total field.

In addition, Access won’t accept any change that might potentially affect many rows in the underlying table. For that reason, you can’t change any of the data in a total query or in a crosstab query. Access can’t update data in a field that has a Sum or Avg setting when the result might be based on the values in many records.

When working with a recordset that is the result of a join, Access lets you update all fields from the “many” side of a join but only the nonkey fields on the “one” side, unless you have specified Cascade Update in the relationship. Also, you cannot set or change any field that has the AutoNumber data type.

The ability to update fields on the “one” side of a query can produce unwanted results if you aren’t careful. For example, you could intend to assign an order to a different customer. If you change the customer name, you’ll change that name for all orders related to the current customer ID.

What you should do instead is change the customer ID in the Orders table, not the customer name in the Customers table.

Query Fields That Cannot Be Updated
Some types of query fields cannot be updated.

  • Any field that is the result of a calculation
  • Any field in a total or crosstab query
  • Any field in a query that includes a total or crosstab query as one of the AutoNumber fields
  • A primary key participating in a relationship unless Cascade Update is
  • Any field in a Unique Values query Any field in a UNION query

Nulls and Zero-Length Strings (V2 and later)

Posted April 22, 2011 By Mike

Relational databases support a special value in fields, called a Null, that indicates an unknown value. Nulls have special properties. A Null value cannot be equal to any other value, not even to another Null. This means you cannot join (link) two tables on Null values. Also, the test “A = B,” when A, B, or both A and B contain a Null, can never yield a true result. Finally, Null values do not participate in aggregate calculations involving such functions as Sum or Avg. You can test a value to determine whether it is a Null by comparing it to the special keyword NULL or by using the IsNull built-in function.

In contrast, you can set Text or Memo fields to a zero-length string to indicate that the value of a field is known but the field is empty. You can join tables on zero-length strings, and two zero-length strings will compare to be equal. However, for Text, Memo, and Hyperlink fields, you must set the Allow Zero Length property to Yes to allow users to enter zero-length strings. Otherwise, Access converts a zero-length or all-blank string to a Null before storing the value. If you also set the Required property of the Text field to Yes, Access stores a zero-length string if the user enters either “” or blanks in the field.

Why is it important to differentiate Nulls from zero-length strings? Here’s an example: Suppose you have a database that stores the results of a survey about automobile preferences. For questionnaires on which there is no response to a color-preference question, it is appropriate to store a Null. You don’t want to match responses based on an “unknown” response, and you don’t want to include the row in calculating totals or averages. On the other hand, some people might have responded “I don’t care” for a color preference. In this case, you have a known “nothing” answer, and a zero-length string is appropriate. You can match all “I don’t care” responses and include the responses in totals and averages.

Another example might be fax numbers in a customer database. If you store a Null, it means you don’t know whether the customer has a fax number. If you store a zero-length string, you know the customer has no fax number. Access gives you the flexibility to deal with both types of “empty” values.


Posted April 22, 2011 By Mike

Although Access 2000 generally interprets 21st century dates with two-digit years correctly, I strongly recommend that you take advantage of the new Use Four-Digit Year Formatting option in Access to avoid all confusion. When you choose this option, Access displays four-digit years in datasheets, forms, and reports. It also converts whatever you type in an expression (such as Criteria in a query) to display four digits.  Choose Options from the Tools menu, and then click the General tab to set this option. I also recommend that you change your Regional Settings in Windows Control Panel to display a four-digit year in the Short Date Style. This will assist your entry and display of year values in other applications — such as Access 97.

Also check out this excellent whitepaper at FMS, Inc.:
Y2K Issues in Microsoft Access