Access 2000

HIRING A NEW ASSISTANT

Office 2000 installs only one assistant by default, and that's Clippit. If you want to fire Clippit and hire a new assistant, you may need to install it first. First, try changing the assistant by right-clicking Clippit, selecting Choose Assistant, and then clicking the Gallery tab (if necessary). Click the Next and Back buttons to find a new assistant, and then click OK. You may want to have your Office 2000 CD ready, just in case.

If you didn't install the other assistants, you'll have to do so before you can dump Clippit and hire a new one

INSTALLING ASSISTANTS

In our previous tip, we mentioned that you may need to install a new assistant before you can get rid of Clippit and switch to another assistant. That's because Office 2000 installs only Clippit. To install another, or all the available assistants, select Start, Settings, Control Panel. Next, double-click the Add/Remove Programs option, select Microsoft Office 2000, and then click the Add/Remove button. Next, click the Add Or Remove Features button in the Office setup window. You'll see a series of options, with plus and minus signs to the left of each. Click the plus sign to the left of the Office Tools option. Then, click the plus sign next to the Office Assistant option, which will open all the available assistants. Select the assistant(s) you want to install, and then choose the installation process you want. Finally, click Update Now and click OK. If you chose the Installed On First Use option, be sure to have your Office 2000 CD handy when you change the assistant.

SHRINKING YOUR ASSISTANT

Let's suppose you like the Office Assistant and you want to keep it. However, it's a little larger than you'd like it. If this is the case, you can reduce the size of your assistant. Display the assistant and then position the mouse pointer over any of the assistant's borders and click. Doing so will reduce the assistant. To enlarge it again, repeat the process.

SMART BACKUPS

Using the default My Documents file folder is a great idea if you have to back up your work on a regular basis. With all your files automatically filed in one folder, you need only back up that one folder. To do so, you'll simply pull that folder to your floppy drive or zip drive (or whatever medium you're using to store backup files).

PUT A BORDER AROUND IT

A border adds a nice professional touch to almost any report. However, no built-in feature prints a border around your report, so you'll need VBA. Fortunately, the code is short and easy to use. Simply attach the following code to the report's Page event:

Private Sub Report_Page()
Me.Line (0, 0) - (Me.ScaleWidth, Me.ScaleHeight), , B
End Sub

BOUND BEHAVIOR

When you update a bound control, Access opens the data source internally. You won't see it happen, of course, but the table opens just the same. If you don't close that table, you could create an error. You see, the next time you try to modify the table, Access will return an error because the table is already opened and locked.

So, how could you create this problem in the first place? You could force Access to update a field. For instance, you might run a quick VBA statement similar to

Me!fieldname = "newentry"

to change a particular field. Unfortunately, that statement alone would open and lock your data source.

If you must update a field in this way, remember to unlock the table afterward. The easiest way to do so is simply to move to the next record using a statement in the form

DoCmd.GoToRecord acDataForm, Me.fieldname, acNext

Thanks!

BREAKING UP IS EASY TO DO

When you need to display a message, you probably use the MsgBox() function. Unfortunately, you can't control how Access displays it. For instance, if your message is long, Access, not you, determines where to wrap it. However, you can take back control using the Chr() function.

To experiment, simply open the Debug window (the Immediate window in version 2.0), enter the statement

MsgBox("This message is too long so" & Chr(13) & "we added a Chr(13) function to wrap the text.")

and then press Enter. Access returns a message box displaying two lines of text. Furthermore, the Chr(13) function inserts a line break between the words "so" and "we." Without this function, Access, not you, decides whether and where to wrap the text.

ACCESS 2000 BUG

Microsoft has identified a problem you may experience with Access 2000. The problem occurs when Access 2000 improperly evaluates an If Then Else expression comparing a variable that is null. Microsoft's solution is to use IsNull() check on variables in the If Then Else expression. Rewrite the expression to include IsNull(), as in the following example from Microsoft.

The expression below will not evaluate properly if strName or txtName is Null.

If strName = txtName then
strName = '1'
Else
strName = '2'
End if

Change the expression to:

If strName = txtName and Not IsNull(strName) and Not IsNull(txtName) then
strName = '1'
Else
strName = '2'
End if

MICROSOFT ACCESS 2000 BUG

Microsoft has identified a bug in Access 2000 that results in the error message "Error Message 2046 Calling OpenForm or OpenReport with Access Automation." When you automate Microsoft Access and call either the OpenForm or OpenReport method, you receive one of the following errors: "Run-time error '2046': The command or action 'OpenForm' isn't available now" or "Run-time error '2046': The command or action 'OpenReport' isn't available now."

This problem occurs when the Database window is hidden at the time you call the OpenForm or OpenReport method from automation.

You can work around this problem with one of the following techniques:

Make sure the Database window is shown before calling the OpenForm or OpenReport method.
Create a module in the Access database that contains the code to open the form or report, then call that procedure from automation using the Run method.

DISABLING THE CLOSE BUTTON

Access forms include the Windows Close button (X) in the top-right corner. Clicking this button will close your form. You can force your users to close the form a certain way by disabling this button and including some other method for closing the form, such as a command button.

You can use a Windows API to disable this button, but Access provides a much simpler solution. Set the form's Close Button property to No. (The default setting is Yes, which displays the Close button.) Your form will still display the button, but it will be disabled (grayed out). As a result, your users will need an alternate way to close the form, so be sure to supply one.

CHANGE YOUR DEFAULT BUTTON

Most dialog boxes and forms use command buttons to close them. Sometimes you'll see a simple set of buttons with just OK and Cancel buttons. A set of command buttons usually has a default button. For instance, the Save dialog box displays Yes, No, and Cancel buttons--Yes is the default button. This means that once the Yes button has the focus, you can press Enter instead of clicking the button to execute the button's click event. You can create the same effect with your own command buttons by setting the appropriate command button's default value to Yes.

All command buttons have a Default property--the settings are Yes and No. However, only one button in each set can have a Yes setting. If you try to specify a Yes setting for a second button, Access will accept the Yes setting for the second button but reset the first button's Default property to No.

DISPLAYING LARGE BUTTONS

Would you like your toolbar buttons to be a bit larger? You can enlarge buttons by choosing Tools, Customize and clicking the Options tab. Then, select the Large Icons option in the Other section. If you change your mind, simply deselect this option, and Access will return to the default buttons. This option won't affect the size of your screen tips.

JUGGLING CODE THE EASY WAY

Many controls act as a toggle switch--clicking the control simply reverses its current state. For instance, if the control is off (equals False), a click reverses the control's state to on (True). Likewise, if the control is on, a click turns it off. To use a toggle control, you simply add code that responds to the control's current state.

You might be surprised to learn that most of the time you won't need a lot of code for your toggle controls--that is, if you can reduce your control's purpose to a simple Boolean value (True or False). When this is the case, use the Not operator to switch between the two values.

For instance, let's suppose that you want to toggle a control's Enabled property between True and False (on and off). To do so, use the Not operator in the form

control.Enabled = Not control.Enabled

where control represents the name or reference of your toggle control. If the control's current state is true and the control's Enabled property is on (True), then clicking the control reverses that situation by turning the Enabled property off (False). You're not limited to using this technique with properties, but the control's state must reduce to a Boolean value.

ADDING LINE NUMBERS TO CODE

You probably know you can comment your code using the apostrophe character or the REM statement. Did you know that you could number your lines of code? You can, and it's easy. Just add the number to the beginning of each line of code. For instance, the procedure

Private Function GetCount()
Dim db As Database, strSQL As String, rst As Recordset
Set db = CurrentDb
strSQL = "SELECT Count(*) FROM table3"
Set rst = db.OpenRecordset(strSQL)
Debug.Print rst(0)
End Function

becomes

Private Function GetCount()
10 Dim db As Database, strSQL As String, rst As Recordset
20 Set db = CurrentDb
30 strSQL = "SELECT Count(*) FROM table3"
40 Set rst = db.OpenRecordset(strSQL)
50 Debug.Print rst(0)
End Function

There are only a few rules to remember when numbering your code:

Numbers must appear at the very beginning of your line of code.
Each number must be unique within the module (which is a nuisance).
Don't number the beginning or ending statement.

HIDE YOUR COLUMNS

Don't use the Column Count property to hide a column in a combo or list box. Instead, set that column's width property to 0". For instance, if you want to hide the first and fourth column in a four-column box, you'd use the Column Width property 0";1";1";0". This property hides the first and last column and displays the second and third column in one-inch columns.

PICK FROM THE COMBO LIST

You use a combo box to display a list of items from which a user can select one or more. A combo box looks just like a text box, except that it has a down arrow at its right border. To see the combo list, click that arrow to open the control's drop-down list. When you select an item, Access displays that item in the control's text box component.

However, you don't have to choose an item from the list--you can type it yourself in the text box component. To force users to choose from the list instead of entering data themselves, set the combo box's Limit To List property to Yes. The default is No--this setting accepts an entry that's not on the list.

If you choose the No setting, the control accepts entries that aren't in the list; a setting of Yes limits users to the items in the control's list. If you attempt to enter an item that isn't in the list when you've set the property to Yes, Access displays an error message warning that you can't enter a non-list item. When you receive this message, click OK to clear the message, and then press Esc to delete the offending entry.

The error message can be a nuisance, and, fortunately, you can suppress it. To do so, attach the following code to the control's NotInList property:

Private Sub comboboxname_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
End Sub

The next time you enter an item that's not on the list, Access still won't accept the value, but it won't display the error message, either. That's because the acDataErrContinue (intrinsic) constant tells Access to ignore the error and continue.

COMPILING DURING DEVELOPMENT

While you're still in the development stage of an application, don't use the Compile All Modules command. The first time you make changes to any of your code, you will undo the compile, so a Compile All Modules command at this stage of the game is a waste of time. When you need to compile code during the development stage, use the Compile Loaded Modules option. This command compiles only the modules that are called by the open module.

CAN'T USE CONSTANTS IN ACCESS OBJECTS

By design, you can't use intrinsic constants in queries, forms, and reports. You can use only VBA constants with VBA code. However, there's a simple workaround--use the constant's numeric value instead. Most of the time, this quick alternative will work just as well. The downside: The numeric values aren't as readable and they may not properly convert to a new version.

If you don't know the numeric value of an intrinsic constant, you can search Help. Alternatively, you can open the Debug window (press Ctrl-G) and run a statement in the form

?intrinsicconstant

(where intrinsicconstant represents the constant) to return its numeric value.

UNFORGETTABLE CONTROL

It's easy to forget about the Control menu--the Access key icon at the left end of the application title bar. Clicking this icon opens a menu that provides a list of commands for sizing and positioning the application window. Specifically, you can use these easy-to-access commands to minimize, maximize, restore, and even close the application window quickly.

CHANGING A CONTROL'S DEFAULT PROPERTIES

Controls have default properties just like tables, forms, and reports do, and you'll probably find yourself altering those defaults on occasion. If you're changing the same defaults too often, you can temporarily change those defaults. To do so:

In Design View, open the Toolbox and click a control button.
Before you insert the selected control, click the Properties button to open that control's default property sheet.
Modify any of the available properties.
Insert your modified control.

Access will retain these new default settings while you're working with the current form--even if you close the form and reopen it later.

CHANGING A CONTROL'S NAME

It's a good idea to name a control as soon as you create it, if you plan to change the default name. If you decide to give the control a more descriptive name later, you'll have to update all the code that references that control. Most important, you'll need to update the control's event procedures. VBA won't update these for you, which explains why your control ceases to work after you change its name. To update the control's code, locate the original procedure in the module and change the name. For instance, if you rename a command button named Command0 to cmdOpen, you should find the original procedure name

Command0_Click()

and rename it accordingly

cmdOpen_Click()

ADD MULTIPLE CONTROLS THE EASY WAY

Generally, you add controls to a form or report by clicking the appropriate control button on the Toolbox in Design view and then clicking the form or report. If you want to add a second (or several) controls of the same type, you probably return to the Toolbox and click the same button before inserting the additional control. Fortunately, this repetitive task isn't necessary.

If you want to add several controls of the same type to a form or report, simply double-click the control button instead of using a single click. Double-clicking a control button temporarily selects that control, so you can insert as many controls as you like without clicking that button again on the Toolbox. To reset the current tool selection, click another control button or the Selection arrow on the Toolbox.

MULTIPLE LINES IN CONTROLS

Do you need to enter more than one line of data in a control? Most of the time, users choose to use memo fields if they have large amounts of text to enter. As you type, the memo field control will wrap your text just like a text editor.

If you need to enter more than one line of text into a text field, a text box won't wrap the text for you. However, you can still enter more than one line of text into the bound text field. Here's how:

Begin by typing the first line of text in the text box control. Press Ctrl-Enter; the text box will position the cursor at the beginning of a new line. Then, type the second line of text.

You can enter as many lines as you like. Simply press Ctrl-Enter when you're ready for a new line. Just remember that you're still limited by the bound field's Field Size property.

WHEN YOU CAN'T CONVERT

Although most files easily convert to Access 2000, occasionally you will see the error

Microsoft Access can't open or convert this previous version database.

This means that you don't have the appropriate security permission to open or convert a database created in an earlier version. If this happens to you, try reviewing the Knowledge Base article Q232589 ACC2000: Error Message "The Current user account doesn't have permission to convert or enable this database."

In a nutshell, there are three solutions you can try:

Ask the owner of the file to convert it for you.
Change the permissions on the system tables to include Administer.
Import the objects into a new database.

THE FASTEST COUNT

We've shown you how to count records in a recordset by using the RecordCount property. However, to do so, you must populate the entire recordset by executing the MoveLast method. If your recordset is large, this is inefficient and unnecessary because the SQL Count function is faster. For instance, the procedure

Function GetCount()
Dim db As Database, strSQL As String, rst As Recordset
Set db = CurrentDb
strSQL = "SELECT Count(*) FROM table3"
Set rst = db.OpenRecordset(strSQL)
Debug.Print rst(0)
End Function

is faster than

Function GetCount()
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("table", dbOpenDynaset)
rst.MoveLast
Debug.Print rst.RecordCount
End Function

Granted, in a small database, you may not notice the difference. However, if you have thousands of records, you should definitely notice an improvement.

THE TWO COUNTS

In our previous tip, we showed you how to use the SQL Count(*) statement to return the number of records in a recordset quickly. If you're tempted to replace the * character with an actual field name because you think referencing a specific field will be faster, don't. You see, the Jet has special optimization rules for the * character, and it actually runs faster than specifying a field name. Only specify a field name in a SQL Count function when you need a count of that particular field.

DAO/ADO CONFLICT

If you're running DAO code in Access 2000, you probably know you need to add the DAO library (Microsoft DAO 3.0, 3.51, or 3.6 Object Library) to your references by choosing Tools, References (in the Visual Basic Editor). If your DAO code still returns an error, remove the Microsoft ActiveX Data Objects 2.0 or 2.1 Library from your references, and your DAO code should run just fine (provided there are no programming errors).

OPENING FOREIGN DATA

Access 2000 makes working with foreign data easier than ever. If you open a foreign file, Access 2000 will automatically create a new database and link to the data, which can save you a lot of setup time. You can work with dBASE files, spreadsheets, text files, and Paradox tables. Of course, the data will need to be arranged in tabular format with rows and columns before you open that file in Access. In addition, the foreign file should contain the same type of data in each column and the same number of fields in each row.

Once you've launched Access, click the Open tool and locate the foreign file. Once you select the file and click OK, Access will create the database and set the links. If Access displays a link wizard, simply follow the instructions to complete the link. This capability isn't available with Access projects.

OPENING A DATABASE

You probably know that the File menu shows the most recently used databases. That means, instead of using the File, Open command, you can simply click one of these databases at the bottom of the File menu and Access will open it. The one drawback that we've found is that these files aren't available using the RunCommand macro action--which means you can't include the most recently opened files in a macro.

QUICK DATABASE PROPERTIES

You can choose File, Database Properties, and Access will display properties for the current database. However, the menu bar isn't always available. If you find yourself in this situation, try right-clicking the Database window title bar. Then, choose Database Properties from the context menu. Access will respond by opening the same tabbed dialog box.

DATABASE SHORTCUTS

You can perform several tasks from the Database window. To see a complete list, right-click the Database window's title bar. Here are just a few of the available tasks:

Open another database.
Import, link, and export objects.
Modify the current database's startup options.
Modify the current database's properties.

Other methods require a few more mouse clicks.

A CLICK TO THE DATABASE WINDOW

When working in a module, you may need to access the Database window to review another object. To do so, you can press F11. However, if you prefer a mouse shortcut, simply click the Database Window tool on the Visual Basic toolbar.

DATABASE WINDOW DEFAULT

By default, Access will open an object if you double-click it in the Database window. A single click simply highlights the object. After selecting an object with a single click, you can click the Open button on the Database window toolbar to open that object or double-click it.

You can change this behavior by choosing Tools, Options, then clicking the View tab. The Click option in Database window has two options: Single-click Open or Double-click Open.

If you prefer to open objects with just a single click, choose the Single-click Open option. Once you do, resting the mouse pointer on an object will highlight (select) that object and a single click will open it.

WHEN TO ELSE OR ELSEIF

The block If allows any number of conditional checks. First, the If statement itself specifies a condition. If that condition isn't met, control will pass to an ElseIf or Else clause. (You can also omit both and simply end the statement with an End If clause.) How do you know which to use? If you want to specify additional conditions, use ElseIf in the form

If condition1 Then
...
ElseIf condition2 Then
...
ElseIf condition3 Then
...
End If

You'll use the Else clause when you want to catch what falls through the cracks. That's because the Else clause doesn't accept a condition. For instance, we could add an Else clause to the above example:

If condition1 Then
...
ElseIf condition2 Then
...
ElseIf condition3 Then
...
Else
...
End If

Any value or expression not caught by condition1, condition2, or condition3 will be handled by the Else statement.

The thing to remember is that you can't add an ElseIf clause after the Else clause. Every If statement can have numerous ElseIf clauses, but only one Else.

CLICK EVENT ORDER

When you click on most controls, you produce a Click event. Right? Well, let's see. When you click a text box, the control fires its Click event--that much is true. However, if you click inside the text box component of a combo box, you don't trigger that control's Click event. So what's the difference? The combo and list box controls don't fire their Click event until the user selects an item from the control's list. This behavior is considerably different from other controls because they trigger their Click event as soon as the control receives the focus. So consider the outcome carefully when you attach code to a combo or list box's Click event.

EVENT OR PROPERTY

When you open an object's properties sheet, you'll see that object's events listed in the form

On event

As a result, you may think On is also part of the event's name, but it isn't. Anytime you see an event prefaced with On and a space between each word in the name, you're looking at the event's property name. In contrast, the actual event doesn't contain the word On and there's no space between the words. For example, On Click is the Click event's property. In addition, On Mouse Move is the MouseMove event's property.

MISSED EVENTS

Changing a control's value programmatically is a fairly common and useful task. But there's something you should know if you choose this route. Changing a control's value triggers both the BeforeUpdate and AfterUpdate events. However, when you change a control's value programmatically, the control doesn't trigger either of these events. So you'll want to be careful about placing code in these events when the control might be updated by VBA.

The form's BeforeUpdate and AfterUpdate events are still triggered if you alter a control programmatically.

HEADERS AND FOOTERS COME IN PAIRS

When you add a header to a form, you'll also get a footer, because they come as a pair. It doesn't matter whether you need both; you still get both. To eliminate an unnecessary header or footer, you need only close it. To do so, select that section and open its property sheet by clicking the Properties button. Then, set the Visible property to No.

HIDING ENTRIES AS YOU TYPE

Do you have to enter sensitive data--information that you wouldn't want anyone else to see? If you're using a data entry form, then use the control's Input Mask property to protect that data from being seen by prying eyes. Specifically, set the control's Input Mask property to Password. Then, as you type an entry, Access will display an asterisk character--instead of the actual character--for each character you type.

ID PLEASE

It's easy to add an identifying prefix to an AutoNumber field using the Format property. Why would you want to? To make the entity that value represents easier to identify.

For instance, let's suppose you use an AutoNumber field to assign a unique number to each order record. In this case, you might want to prefix each number with the string OrderID or OID. To do so, open the table in Design view and select the AutoNumber field. Then, use the expression

>OrderID

as the Format setting. You can use this technique with any field--we just find it particularly useful with AutoNumber fields.

CHOOSING MACRO ACTIONS

Macros automate a database by repeating actions and tasks for you. To create a macro, you open the macro window and enter a macro action in the Action column. There are three ways to enter a macro action:

Type it directly into the Action cell.
Choose the action from the Action cell's drop-down list.
Drag an object from the Database window to the macro window.

Now, you're probably familiar with the first two options, but the last option might be new to you. Once you've opened the macro window, click the Database Window button on the Macro Design toolbar. Then, drag an object from the Database Window into the macro window. Access will automatically default to the appropriate open action and enter the dragged object's name as that object's name property.

CONDITIONAL FORMATTING

Access 2000's conditional formatting feature is a big improvement over Access 97's format offerings. However, you can still use VBA instead. For instance, let's suppose you want to display certain report data in bold, depending on the contents of a second control. You could use the conditional formatting feature or you could attach code, similar to the following, to the Detail section's Print event:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me!chkName Then
Me!txtName.FontWeight = 700
Else
Me!txtName.FontWeight = 400
End If
End Sub

The above procedure will print the contents of a text box in bold print when a second control evaluates to True.

When chkName (which represents a checkbox) equals True, Access bolds the contents of txtName (which represents a text box). If the checkbox is False, Access doesn't bold the contents.

You can easily adjust this procedure to fit most any condition. Simply update the control names, properties, and settings, and specify an appropriate condition.

A CONDITIONAL MACRO

VBA doesn't have any trouble making decisions. You can use If or Select Case to check for conditions and direct your code accordingly. You might not realize that macros can make decisions, too. For example, let's suppose you want to open a form. In addition, you want the option to cancel the entire macro instead of opening the form. In this case, you might use the InputBox() function to determine whether the macro continues (and opens the form) or quits. First, open the Conditions column by clicking the Conditions Columns button on the Macro Design toolbar. Next, enter in the Conditions column the expression

InputBox("Do you want to open the form?") = "Yes"

When you run the macro, this expression will display an input box. If you respond to that box by entering the string "Yes", the macro will continue. If you enter anything else, the macro will quit.

UNDO FORM CHANGES

Access 2000 allows you to undo all changes made since the last save to the current form. With the form open in Design view, simply choose File, Revert, and Access will undo any changes you've made to the form since the last time you saved it.

If Revert is disabled, no changes have been made since the last save.

OPENING A DATA ENTRY FORM WITH A MACRO

In our previous tip, we showed you how to use VBA to open a form in data entry mode. If you'd rather use a macro, you can. First, open a blank macro window. Specify the OpenForm Action, and identify the form by name in the Form Name property. Next, specify the Form View setting, then choose Add from the Data Mode property field.

TEMPORARILY DISABLING A MACRO

An AutoExec macro runs when you first launch a database. If you want to disable the AutoExec macro once, you can hold down the Shift key when you launch the database. Occasionally, you may find yourself wanting to run a series of tests without activating the AutoExec macro each time you open the database. In this case, remembering to hold down the Shift key can become a nuisance. Perhaps the easiest way to temporarily deactivate a macro for a prolonged period of time is simply to change the macro's name. You see, an AutoExec macro must be named AutoExec. Renaming an AutoExec macro will keep Access from running it again until you change the name back to "AutoExec."

CUSTOM DATE FORMATS

Formatting a date is easy using the Format() function. But did you know that you can create a custom date format? You simply put together the appropriate code argument. For instance, the "d", "mmm", and "yy" codes display the day, the abbreviated month, and a two-digit year value, respectively. By putting them together as one code argument--"dmmmyy"--you can display March 3, 1999, as 3Mar99.

You can also display a literal character--such as a comma and space character--to create the string March 3, 1999. Just use this format function:

Format([DateField],"mmmm d"", ""yyyy")

We added a comma and space character by simply including those characters in the code. Just be sure to enclose any literal character in double quotes, as shown.

TAKE THE PAIN OUT OF REMEMBERING YOUR LAST DATE

A quick way to determine the first or last date in a date field is to run a Totals query. After specifying the query's group, select the First or Last aggregate function for the query's date field and then run the query. Doing so returns the first or last date, accordingly, for your group of records.

To specify a Totals query, open the query in Design view and choose Totals from the View menu. Doing so will display the Totals field in the query design grid. Open this field's drop-down list to see a list of aggregate functions, including First and Last

DISPLAYING FOUR-DIGIT YEAR DATES

Access 2000 takes the guesswork out of displaying four-digit years in your date fields. Now, you can force all your controls and fields to display four-digit years with just a few clicks. By displaying all four digits, you can ensure that a date's century is the right one with just a quick glance. To enforce this behavior, you'll need to access the Options command from the Tools menu and then click the General tab. In the Use Four-Digit Year Formatting section, you'll find two options:

This Database: Sets the four-digit formatting option for the current database.
All Databases: Sets the four-digit formatting option at the application level and affects all databases and Access projects.

Both options will override any Format property settings you've defined for individual fields and controls.

ENABLED VERSUS LOCKED

It's easy to assume the Enabled and Locked properties are the same. Even Help lists the two together. Although they're similar in some respects, they aren't the same and they aren't interchangeable. You'll use the Enabled property to determine whether a control can have the focus in Form View. Specifically, a Yes setting means you can set the focus to that control; a No setting means you can't. For the most part, that means you can't select the control in Form View--even if the control's Tab Stop property is set to Yes.

The Locked property will keep users from changing the contents of a control. You can still set the focus to the control, meaning you can select it. However, you can't change the control's contents.

IMPORTING MORE THAN OBJECTS

To import a table or form from another database, you probably use the Import command. Specifically, you choose File, Get External Data, Import, and then identify the database that contains the object you want to import. Next, you select the object and click OK.

You might not realize you can import a lot more than objects. For instance, you can import toolbars and relationships, and you can even import and export specifications. You'll find these import tasks by clicking the Options button in the Import Object dialog box. Thanks to a reader for this helpful tip.

DETERMINING A LEAP YEAR

With all the noise about Y2K bugs and leap years, we thought you might need to know how to tell if a particular year is a leap year. You can use an expression in the form

(year Mod 4 = 0) - (year Mod 100 = 0) + (year Mod 400 = 0)

to determine whether year is a leap year. If the year is a leap year, the expression returns -1; if it isn't, the expression returns 0. We've seen a lot of expressions, some shorter, but this is the only one we've come across that always works.

MORE ON CLOSING ALL MODULES

A few tips ago, we suggested that you close your database after running the Compile All Modules command. The truth is, you'll probably want to close your database a couple of times during every work session (if your sessions are long).

When you call a function, Access opens the module that contains your code. Unfortunately, Access doesn't offer a programmatic way to close that module once you've run the procedure. That means you eventually end up with lots of modules open--and consuming memory--long after you need them. To free up memory used by open modules, simply close and reopen your database. (Don't exit Access; just close the database.) Doing so will close all your modules and free up the memory they would otherwise consume.

SAVING YOUR SPOT IN A MODULE

When working with code in the VB Editor, you can return to your form or report without losing your spot in the code module--and you don't have to do a thing to mark your place. The next time you're working in a code module, pay close attention to the position of the cursor before you close the module. After closing the module, reopen it and you'll find the cursor in the same spot you left it in. That way, you can go right back to the code you were working on if necessary.

INSTALLING MSDE

You may have read about Microsoft Data Engine (MSDE)--a new technology that provides local data storage that's compatible with Microsoft SQL Server 7.0. However, you can't install it by running the Office 2000 Setup program.

If you want to add this new functionality to your system, insert the Office 2000 CD 1 and locate the \SQL\x86\Setup folder. Then, double-click Setupsql.exe.

FORMATTING TEXT IN A MSGBOX

In Access 97 you could add formatting codes to a MsgBox function. This feature is still available in Access 2000, but you'll have to use the MsgBox action because the MsgBox function doesn't support the formatting codes in Access 2000. For instance, the following steps will display a message box's first line of text in bold.

First, open a macro window by selecting Macro in the Object bar and then clicking New. Choose MsgBox from the first Action cell's drop-down list. Then, in the Message argument field, enter the following string (don't use quotes as you sometimes do with strings):

This line is bold@This line is plain@This line is plain

Click the Save button on the toolbar and name your macro. Then, click the Run button.

Access will display a message box with three lines of text. The first line, "This line is bold", will appear in bold typeface. The next two lines will both display the text "This line is plain" in normal, or plain, typeface.

NORMALIZING CHOICES

One of the normalization rules states that all fields in a table should relate to that table's primary key. However, sometimes that's a tough call--just how much normalization is enough? For instance, if you're working with a customer database, should you have a table just for customer names, another for addresses, another for phone numbers, and so on? Or should you use just one table to store all your customer information? For the most part, there's no clear advantage to separating all your customer data. There are two situations when you'll want to consider creating a second table:

When a field is optional, meaning many fields may remain blank.
When a field has more than one possible entry.

Let's consider the customer's phone number data. Some customers may not have a phone (not likely, but it is possible). Other customers may have more than one phone, or type of phone. For instance, many businesses have dedicated fax lines and cell phones. In this case, you have three design possibilities:

Store all the customer information, including the phone numbers, in one table.
Create a separate table for phones with a one-to-one relationship.
Create a separate table for phones with a one-to-many relationship.

Now, what are the pros and cons for each solution? Well, a single table will produce quicker data retrieval since you're accessing only one table. However, you may have blank fields and you may end up duplicating data if the customer has more than one phone. In fact, if that's the case, you really have no choice but to create a second table. If you care about storing only one telephone number--say, the customer's direct business line--then the second option will work. The third option is more flexible than the second, but if the additional numbers aren't needed, it's wasted effort. In the end, the choice is yours--being familiar with your data is the fastest ticket to good normalization.

PLANNING FOR NULL PARAMETERS

It's easy to use the contents of a control as the criteria in a parameter query. Instead of hard-coding the criteria in your expression, use an expression in the form

Forms!formname!controlname

When you run the query (the form must remain open), Access checks the control for its contents and use that value as the query's criteria. If the control is blank, the query returns a blank record set. If you'd rather return all the records when the control is blank, use the query expression

Forms!formname!controlname OR Forms!formname!controlname Is Null

You can enter this as one expression as shown, or you can break it up and enter the first part in the Criteria cell and the second part in the Or cell.

CATCHING RECORDSET NULLS

You may not anticipate Null values in a recordset, but sometimes they're there just the same. If you aren't prepared for the Null value, your application will come to a screeching halt with the dreaded

Invalid Use of Null

error. To avoid this, you should test field values for Null using the IsNull function in the form

If IsNull(rst!fieldname) Then

where rst represents the recordset and fieldname is the field you're accessing.

ANOTHER QUICK NULL CATCH

Our previous tip showed you an easy way to avoid Null errors by catching Nulls before they have a chance to stop your code. Another quick method for catching Null values is to use the Format property in the form

var = Null
str = Format(var)

where str is a String variable and var is a Variant. The following procedure runs a quick check for Nulls using this method:

Function CatchNull()
Dim var As Variant, str As String
var = Null
str = Format(var)
MsgBox str = vbNullString
End Function

If var is Null, the message box displays True (False when var isn't Null).

Using Format returns a zero-length string; the vbNullString constant, used in this context, will catch a zero-length string.

WHEN A NUMBER ISN'T A NUMBER

We store lots of numerical data we never use in calculations. Phone numbers, Social Security numbers, and addresses all contain numbers, but you'd never try to use this type of data in a mathematical expression. Since you don't need these numbers to act like values, you can save memory by storing this type of data as text instead of numbers. A text field can and often does consume less memory than a number field.

Just remember that your numeric entries will sort differently as text. For instance, the values 1, 2, and 11 won't sort in numeric order.

Instead, these three values will sort as text--1, 11, and 2.

OBJECT SHORTCUTS

You probably know how to create shortcuts to files, but did you realize that you could create a shortcut to an Access object? Access objects are stored together in the same .mdb file. That means you must launch Access and open the database to work with an Access object--say, view a report. If you routinely work with the same object, a shortcut may be just what you need. To create a shortcut to an Access object, right-click its icon in the Access Database window and choose Create Shortcut. Access will offer to save your shortcut on the Windows desktop. You can go with this option, or click the Browse button to find a new location.

COPYING OBJECTS

You can copy an object without using the Edit menu's Copy and Paste commands. Simply select the object you want to copy and hold down the Ctrl key while you drag that object to the end of the object list and then release it. Access will create a duplicate and name it "Copy of filename." For example, if you want to copy a report named MyReport, you'd select it, hold down the Ctrl key, and drag MyReport to the end of the report list. Access would then display a new report named "Copy of MyReport" in your report list.

NEW OBJECTS AT A CLICK

There are two ways to use the New Object button: You can click the drop-down arrow to display a list of items, then choose the appropriate object, or you can click the button itself. This button displays the type of object you last selected. To create another object of the same type, simply click the button instead of opening the drop-down list and selecting that object type.

IMPORTING QUERIES

In our previous tip, we told you how to import toolbars and more using the Import command. You can also import a query as a table. To do so, choose File, Get External Data, Import. After identifying the database that contains the query you're importing, select the query you want to import as a table. Next, click the Options button in the Import Object dialog box and select the As Tables option in the Import Queries section. Any query you import with this option selected will take the form of a table in the importing database.

DEFAULT VALUES IN PARAMETER QUERIES

A reader sent us a great tip for using default values in parameter queries. As is, you can't specify a default value for a parameter in query. For instance, let's suppose you use the same parameter query to check sales by region. When you run the query, the parameter expression prompts you for a region. You enter a region, and Access will then display the results for that region. Now, let's further suppose that you run this query for one particular region most of the time. In that case, it would be nice if your parameter query would automatically default to that region. Then, you can run it by just accepting the default--changing it only when you needed to see the sales totals for another region.

He explains that if you use the InputBox function in your parameter expression in the form

Eval("InputBox(""prompt:"",""title"",""defaultvalue"")")

Access will display an input box instead of the normal parameter prompt--with the default value. If you click OK, Access will run the query using defaultvalue as the query's criteria. Or you can enter a new value and click OK to run the query.

QUERY LIMIT

Access limits a query to 255 fields. We can say this with a smile on our face because, frankly, most queries will never reach this limit. However, it isn't impossible to have a query so large and complex. If one of your queries should reach the limit, you do have a few alternatives. First, try the most obvious solution and eliminate unnecessary fields. This should be easy if you used the asterisk character to add fields to the query design grid. Chances are there are a few fields you don't really need but are included only because you used the asterisk character (which includes all fields). Second, if your query is the data source for a form or report, consider adding a subform or subreport to the main object and splitting your query in two, accordingly.

BYPASS PARAMETER QUERY

We often base reports and forms on parameter queries. You can get a similar effect by adding a parameter to a report control. For instance, let's suppose you want to display a particular date in your report, but the date will change from report to report. An easy solution is to add a control, name it txtDate (or whatever you like), and then add the Control Source setting of

[Enter letter date]

or something similar. When you run the report, Access will prompt you for a date. You'll enter one and click OK, and as a result, txtDate will display the date you entered in your report.

This solution won't act as a filter for your report--it's simply an easy way to display changing information.

If you decide to test this technique, open an existing report; itwon't work in an unbound report.

USING THE SIMPLE QUERY WIZARD--PART 1 OF 2

Queries aren't your cup of tea? Don't worry. If you can't make heads or tails of the query design grid, make the Simple Query Wizard your new best friend. Although this wizard is a bit limited--it retrieves data but doesn't limit data--it can make quick work of many of your query needs.

There are several ways to launch the Simple Query Wizard:

Click the Queries tab in the Database window, select New, and then double-click Simple Query Wizard in the New Query dialog box. (Or select the wizard and click OK.)
Click the Tables tab in the Database window, select the table you want to base your query on, choose Query from the New Object button's drop-down list, and then double-click Simple Query Wizard.
In the Database window, select the table you're basing your query on; choose Insert, Query; and double-click Simple Query Wizard.

USING THE SIMPLE QUERY WIZARD--PART 2 OF 2

When using the Simple Query Wizard to create your query, you can include more than one table or query in your query. Simply specify the first table or query by selecting that table or query from the Tables/Queries control. If you want to include a second table or query, simply select an additional source from the Tables/Queries control. The wizard will add all the fields from any table or query you select in this manner.

RENAMING OBJECTS

In our previous tip, we told you how to copy a database object quickly using the Ctrl key. When you use this technique, Access creates a copy of your object and names it "Copy of filename."

Once you have a copy of an object, you'll probably want to rename it. There are a few ways to do so:

Right-click the object in the Database window.
Choose Rename from the resulting shortcut menu.
Enter the new name.

Or you can rename the object right in the Database window:

Slowly double-click the object in the Database window. When we say slowly, we mean to apply two single clicks, one right after the other, but not so quickly as to constitute a true double-click.
Access will enter the object name in Edit mode--you can tell by the thin border that Access displays around the object's name.
While in Edit mode, simply enter the new name and press Enter when you're done.

EDITING THE OFFICE 2000 DICTIONARY

In our previous tip, we told you how easy it is to add a word to the Office 2000 dictionary while running the Spelling feature. You can also edit the dictionary directly, and doing so is almost as easy as clicking the Add option in the Spelling dialog box.

First, launch Word and choose Tools, Options. Click the Spelling And Grammar tab, choose Dictionaries from the Custom dictionary list, and select Edit.

Enter each new word or term on its own line.

IMPROVING PERFORMANCE

Speed is an issue with any application, and we've made many suggestions to enhance speed over the past several months. An easy way to improve an application's performance is to split it into two databases. Specifically, you should separate the interface objects (forms, reports, queries, etc.) and your data (tables). Access comes with a feature that will move your tables to a new database, known as the back-end, and then link those tables to your interface objects, known as the front-end.

To split your database, choose Tools, Database Utilities, Database Splitter. In the resulting window, click the Split Database button. The feature will then display the Create Back-end Database window and supply a default name, which you can change if you like. Click Split to continue. Be aware that the process can take a few minutes.

Before you split your database, you should make a backup of your application, just to be safe.

PRINTING PARAMETERS

If you've based a report on a parameter query, you can print the parameters as part of your report. For instance, if your parameter is a date, you can print that date in the header of your report. Simply add a text box to your report's header and specify the parameter by name as the control's ControlSource using the syntax

=Reports![reportname]![parametername]

Let's suppose you want to add the feature to a report named rptDates and your query's parameter is [Enter Date]. In this case, you'd enter the expression

=Reports![rptDates]![Enter Date]

A QUICK PRINT

You can quickly print a form and its code from the VB Editor. Simply select the form you want to print and then choose File, Print on the VB Editor's Standard toolbar. The VB Editor will then display the Print dialog box, which will offer several options. You can print the form's image, its code, or both. You can even change the print quality (the default is High) or send the form to a print file.

ADO CURRENT PROJECT

We've had a lot of people ask how to use ADO to connect to the database. That's because ADO is typically used with foreign data (although we'll all be using it in the future as Microsoft plans eventually to replace DAO with ADO). As a result, most examples and documentation stick with connections to files other than the current file. If you'd like to use ADO in the current database, use the following code to open a connection to the database:

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset Set cnn = CurrentProject.Connection

Then, refer to your cnn variable when you open your recordset in the form

rst.Open source, cnn, cursortype, locktype

where source identifies your data source, cursortype is one of four constants that identify the position of your cursor, and locktype is also a constant that specifies your locking preference. As a rule, you'll use adOpenKeyset and adLockOptimistic, respectively. Now, let's suppose you want to open a recordset based on a table named tblMyWork in your current .mdb file. To do so, you'd use the code

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset Set cnn = CurrentProject.Connection
rst.Open "tblMyWork", cnn, adOpenKeyset, adLockOptimistic

You can replace the cnn variable with the connection reference in the form

rst.Open "tblMyWork", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

MODELESS PROPERTIES WINDOW

When working in Design View, you probably open the Properties sheet to modify object properties. Once you modify a property, do you close it and then reopen it again if you need it? If so, you don't need to. The Properties window (sheet) is modeless, which means you can access other objects while it's open. In fact, leaving the window open is very convenient, because the window displays the properties of the selected object. That means that you can jump from one object to another without having to update the Properties window.

RECONSTRUCTION

When you copy a SQL string from the SQL window to a module window, it's usually in block form instead of being one long statement, which VBA requires. Consequently, you have to assemble all the clauses on one line, and doing so can produce an Expected Case error, which you must clear before you can continue. You can easily avoid this error by reconstructing your statement from the bottom up. In other words, if the SQL statement comprises three separate lines, don't pull the second line around to the first and then pull the third line around to the first. Instead, pull up the third line to the second, and then pull up the combined second line to the first line.

THE ACTIVE RECORD

When scrolling through records in Datasheet view, you're not actually changing the active record. You're just changing the areas of the table you can see. To change the active record, you must press the Down Arrow or Up Arrow key or the Page Down or Page Up key. As you might expect, the Down Arrow key selects the next record, and the Up Arrow key selects the previous record. The Page Down and Page Up keys move the screen and the selection an entire screen. In other words, if the active record is row 2 and the current screen displays 20 records, pressing Page Down would select record 21. You can also use the navigation buttons at the bottom of the table.

COPYING RECORDS

Often, one record will contain much of the same data as the new record you're entering. When this is the case, you have two choices. If most of the fields are the same, consider selecting the entire record and copying it to the new record, then reentering the one or two fields that are different. To select an entire record, simply select the gray row cell to the left of the record. Then, press Ctrl-C to copy the selected record to the Clipboard. Select the new record row and then press Ctrl-V to paste the contents of the Clipboard to the new record.

If only some of the cell entries are repeated, you can select adjacent cells by holding down the Shift key as you click the cells. Then, copy the selected cells to the new record. Depending on the cells you select, you may have to use the Paste Append command from the Edit menu instead of just Paste.

OMITTING DUPLICATE RECORDS

The Find Duplicates Wizard will locate duplicate records. However, most of the time, what we really need is a query that will display only one record when duplicates exist. When this is the case, build your query as you normally would and then set the query's Unique Values property to Yes. Here's how. First, with the query in Design view, click the Properties button or right-click the query to open the property sheet. Then set the Unique Values setting to Yes.

Doing so will eliminate records with duplicate values for all the fields in the query.

COUNTING THE NUMBER OF RECORDS IN A GROUP

Totals queries are good for grouping records by specific field entries and returning calculations based on just those records. For instance, you can use a Totals query to return the total sales for each salesperson.

First, create your basic query based on your sales tables. Drag the appropriate name or identification and value fields to the query design grid. Choose View, Totals, and select Group By from the name or identification field's Total cell drop-down list. Choose Sum from the value field's Total cell drop-down list. Then, run the query.

The resulting query should display one record for each salesperson and a total of all that person's sales.

Now, let's suppose you want to count the number of sales per salesperson. In that case, you'd follow these steps. First, create your basic query based on your sales tables. Drag the appropriate name or identification fields to the query design grid twice. Next, choose View, Totals. Select Group By from the first name or identification column's Total cell drop-down list. Select Count from the first name or identification column's Total cell drop-down list. Then, run the query.

This time, the query will display one row for each salesperson and the total number of sales for each.

PASTING NEW RECORDS

We often copy data from an existing record to a new record to save a little data entry time. If you're using the Copy and Paste commands, we can save you one more keystroke. Instead of using the Paste command, use the Paste Append command. That way, you won't have to select a field in the new record before copying the data.

Let's look at a quick example. First, select any existing record in any table. Then, press Ctrl-C to copy that record to the Clipboard. Now, if you were using the copy/paste method, you'd have to select a field in the new record and then press Ctrl-V to paste that record into the table. Instead, simply select Edit, Paste Append.

OPENING TO A SPECIFIC RECORD

A reader wants to know how to open a form to a particular record. By default, a form displays the first record in the underlying recordset. Perhaps the easiest method is to use the GoToRecord method in the form's Open event. The GoToRecord method uses the syntax

DoCmd.GoToRecord acForm, "formname", acGoTo, recordnumber

where acGoTo is an intrinsic constant and recordnumber is the numeric value that equals the record you want to view--in other words, the record number.

RETURNING THE LAST N RECORDS

Several tips ago, we showed you how to use SQL to return the top n records in your query result. Basically, the SQL method is the same as using the query's Top property. However, there's no corresponding Bottom property to return the last n records in a query.

The trick is to use the Top property and sort the records in descending order. As a result, the records that are generally last are now sorted to the top of the query's results--what was last is now first and vice versa. Consequently, the Top query still returns the first records in the query result.

If you decide to use SQL to return the last records in a query, use a statement in the form

SELECT TOP n field FROM table ORDER BY table.field DESC;

SAVING RECORDS

Many of you use custom data entry forms to enter new data. You enter the data and then click a command button that accepts the data and shows a new blank form for the next entry. However, if you check on the record you just entered, it doesn't appear to be there.

If you're using VBA code to handle your data entry, you must remember to save the record! Simply add the following statement to your command button code:

DoCmd.RunCommand acCmdSaveRecord

Doing so will save your record immediately.

TRACK ALL THE RECORDS

If you need to inhibit the navigation buttons on an Access form, you can still track the total number of records in the underlying recordset. To do so, add a text box to the form and name it txtTotal. Then add the following code to your form's Current event:

Private Sub Form_Current()
Me.RecordsetClone.MoveLast
Me![txtTotal] = Me.RecordsetClone.RecordCount
End Sub

DEFAULT RELATIONS

There's not much you can do about your human relatives, but you have complete control over the relationships in your Access databases. By default, Access creates a relationship between two tables in a query, if the two tables contain a common field of the same name and data type, and at least one of the fields is a primary key field.

If you don't like this arrangement, turn off this feature by choosing Tools, Options, then clicking the Tables/Queries tab and deselecting the Enable AutoJoin option.

DISOWNING YOUR RELATIONS

Access automatically creates a relationship between two tables with AutoJoin if the following two conditions are true:

Both tables contain the same field data type and field size.
One of those fields is a primary key.

Most of the time you'll appreciate this feature. But you can turn it off if necessary. Choose Tools, Options; click the Tables/Queries tab; and deselect the Enable AutoJoin option. Once you do so, you'll have to create relationships in the Relationships window.

PRINTING 97 RELATIONSHIPS

Access 2000 will print a report of your table relationships, which is an easy way to document those relationships. Unfortunately, if you want a similar report for an Access 97 database, you must use an add-in. Or do you?

If you still support 97 databases, you can create a relationship report using Access 2000. Simply convert the 97 database to Access 2000 and print the relationship report by choosing File, Print Relationships.

Just be sure to rename the database when you convert it--you don't want to convert your actual database! You just want a temporary conversion so you can take advantage of the Print Relationships feature in Access 2000.

EXTRA REPORT PAGES

Ever print a report that prints blank pages between each page or every other page? Usually this is an indication that the report is wider than the selected page size. This can happen if a control extends past the margin. When positioning and resizing controls, don't forget about that right margin. For instance, if you're working with a typical 8-inch by 11-inch sheet of paper in a portrait report and both the left and right margins are one inch, you have only six inches--from left to right--to work within. That means all of your controls must be to the left of the 6-inch ruler mark. If a control extends past that mark, Access will print an extra blank page for each page of your report.

Fortunately, this problem is usually easy to resolve. Review the page size and margins and then check all the controls at the right edge of your report. Resize or reposition any controls that extend past the report's right border.

VIEWING MULTIPLE REPORT PAGES

When viewing reports in Print Preview, you generally get one page. If you'd like to see more, right-click the report, choose Multiple Pages from the context menu, and then select the number of pages you want to see. For instance, if you want to see two pages side by side, you'd select the second page in the top row--the 1 X 2 Pages setting. If you want to see three pages side by side, you'd select the 1 X 3 Pages setting, which is the third page in the top row. You can also view stacked pages. Simply select the first page in the second row, the 2 X 1 Pages setting, to see one page on top of another, and so on.

CREATING A ONE-LINE SUBTOTAL REPORT

In our previous tip, we showed you how to add subtotals to a group report. That report displayed all the detail records for each group. Sometimes, a list of subtotals is easier to read and all you really need. Fortunately, this type of subtotal report is easy to create. Referring to our previous tip, simply follow the steps for building a subtotal report. Then, delete all the fields from the Detail section--yes, you read that right. Remember, you don't want the detail records for each group; you just want the subtotals. Trust me, it will work.

Just in case you can't access our previous tip, we'll repeat those instructions today:

Create your report--it needs to be a grouped report.
Open the group footer section and enter the appropriate subtotal function. For instance, if you're wanting to display a total of each region's sales, your function might be similar to =Sum([Sales]).
To create today's one-line subtotal report, delete all the fields from the Detail section and close up that section.

REPORTING IN ACCESS 2000

There are several ways to report data in Access 2000. You can rely on the standard report feature. You can also export a report to static HTML format. New to Access 2000 is the ability to work with report snapshots and to view those reports in the Snapshot Viewer. (Access 97 needed an add-in to view report snapshots.) Also new to Access 2000 is the Pages object--a type of interactive Web page.

Here is a brief description to help you decide which reporting option is appropriate for you:

To print data for distribution: Try the Standard, Snapshot, HTML, or Pages reporting options.
To distribute data by email: Try the Snapshot, HTML, or Pages reporting options.
To distribute data by browser: Try the Snapshot, HTML, or Pages reporting options.
For interactive data in a browser: Try the Pages reporting option.

Access 2000's new Pages object requires Internet Explorer 5 (or later).

BUILDING REPORTS

You can build an Access report by using one of the many report wizards, or you can choose to build your report from scratch. If you have a form already based on the same table or query, you might want to consider a quicker approach. Simply copy the form's controls to a blank report.

First, open your form in Design view and choose Select All from the Edit menu (or individually select the controls you want to copy). Select Edit, Copy or press Ctrl-C to copy the selected controls to the Clipboard. Next, select Report from the New Object button's drop-down list. In the New Report dialog box, select Design View and click OK. Then, select Edit, Paste or press Ctrl-V to paste the controls into the report.

Of course, this shortcut won't always be appropriate, but when it is, it can be a real timesaver.

NUMBERED REPORTS

Sometimes you need to number the records in a report, and depending on a table's AutoNumber value isn't wise. Remember, the AutoNumber values simply track the order in which you enter records. A sorted or grouped report won't be in the same order as your underlying table (most likely).

Fortunately, you can number your records in a report. To do so:

Add an unbound text box to the Detail section. Most likely, you'll want to position this control to the left of your record controls.
Enter a ControlSource property of =1 for the new text box.
Change the textbox control's Running Sum property to Over All if your report isn't grouped. If the report is grouped, select the Over Group property.

When Access runs your report, the new textbox control will display a series of consecutive values, beginning with the value 1.

RELATIONSHIP NAMES

Often, related fields share the same field name, but you might not realize that it isn't necessary. You can relate fields even if they have different names. The only requirement is that both fields contain the same type of data. For instance, you could relate two fields named CustomerID and CustomerNumber as long as their data types match. If, however, Customer ID is a Numeric field and CustomerNumber is a Text field, the relationship won't take.

COMPLETE LIST OF RUNCOMMAND CONSTANTS

The RunCommand method was added to Access 97 to replace the DoMenuItem method. The RunCommand method runs a built-in menu or toolbar command and uses the syntax

object.RunCommand command

where object is the application or the DoCmd object, and command represents the menu or toolbar command. Each command has a constant that you can use, and they're listed in the Help section.

To locate a complete list of RunCommand constants, open Help's Index feature and enter

Run Command Method Constants

If that doesn't work, you can still get there by taking the scenic route. Enter

Run Command

and choose Run Command Action from the third control. At the bottom of this section, click the first RunCommand link. In the resulting window, click the RunCommand Method Constants link in the second paragraph under the Remarks heading. This last link should display a complete list of VB RunCommand constants.

RETURNING A PERSON'S AGE

Many simple expressions are floating around that will return a person's age. We've tested them all and found them all lacking in some area. Eventually, these expressions will return an incorrect age, and you may not know it.

The best route is to go with an accurate expression, even if it's long, like the one we're about to show you. When working with age, you can use an expression in the form

Year(Now())-Year(birthdatefield) + (DateSerial(Year(Now()),
Month(birthdatefield), Day(birthdatefield))>Now())

where birthdatefield is the field that contains your birthdates. The expression may be long, but it's the only one we've found that works in all situations.

AN EASY SEARCH

The Filter By Form data is one of the easiest ways to search for matching data in Form View. Simply click the Filter By Form button on the Form View toolbar and Access will display a blank version of your form. At this point, you enter the data you're searching for in the appropriate control and then click the Apply Filter button on the Form View toolbar. For instance, if you're viewing records in a customer database and you want to see only those customers in the state of New York, you'd enter

New York

in the State control and then click the Apply Filter button.

Access will search the underlying (or bound) recordset and display only those records where the state field contains the entry New York. Access will also display the number of matching records at the bottom of the form along with the string "(Filtered Set)", so you can easily tell when you're working with a full set of records or just the results of a temporary filter.

Once you've viewed the matching records and you're done with the filter, simply click the Remove Filter button, which is actually the Apply Filter button. This button acts like a toggle switch--you click it to apply a filter and then you click it again to remove the filter.

If the Form View toolbar isn't available, you can choose Records, Filter, Filter By Form.

BROWSING A FILTERED SET

In our previous tip, we talked about using the Filter By Form feature to view records that match a specific search string. Many features available in Form View are also available with the Filter By Form form. For instance, if you're working with a single form, you can click the View button and choose Datasheet to view all the records at one time. Fortunately, this feature also gives you a quick glance at all the records in your filtered set. After specifying the search string and displaying the filtered set, simply click the View button and choose Datasheet, as you would in Form View. Access will display just those records that match your search criteria in Datasheet View.

SHARING INFORMATION

When you select a control, Access can display text in the status bar that reflects the purpose of the current control. This behavior provides a good opportunity to define the control or give hints on how to enter the correct data. To display such a message, simply add the desired text to the control's Status Bar Text property. This message can contain up to 255 characters, but Access displays only what fits in the space allowed--so if your status bar is cluttered, you'll need a short message.

OUT OF SIGHT

If you'd rather not see an empty report, you can use the No Data report property. Access fires this property when the underlying table or query contains no records to display. To keep Access from displaying the empty report, attach the following code to the report's No Data event:

Private Sub Report_NoData (Cancel As Integer)
MsgBox "There are no records to display"
Cancel = True
End Sub

AFTER THE SORT

You can use the Sort Ascending and Sort Descending buttons to sort records in ascending or descending order. However, if you need a more complex sort, such as sorting by more than one column, you probably want to use the Advanced Filter/Sort feature. You may or may not want to save your records in sorted order. After sorting, you can return your records to their original order by choosing Records, Remove Sort/Filter.

SORT BEHAVIOR

A Totals query is a special kind of query that allows you to group records by a field or a group of fields. Most often, we use a Totals query to perform calculations on groups. For instance, you might want to subtotal all the sales by region. In this case, you would create a query that contains the region and sales total fields, group by the region, and sum the sales total field.

You can create a Totals query by choosing View, Totals in the query design grid. Then, select an aggregate function in each field's Total cell. There are lots of possibilities.

One thing you want to watch for when working with a Totals query--Access sorts the result, even if you don't specify a sort order. Since Access must group the underlying records, this behavior might seem obvious, but it's easy to miss if you don't realize it's happening.

A CHECK FOR THE SPELL CHECKER

Finding typos when you know you ran the Spell feature? It's possible because, by default, Access will ignore words that are in uppercase letters or that contain numbers. If this is causing you a problem, you can modify the spelling option. To do so, run the Spell feature on data you know will produce the error. When Access displays the Spelling dialog box, click the Options button. Clear the Words In UPPERCASE option in the Ignore section. Then, clear the Words With Numbers option in the Ignore section. Finally, click OK and then click Cancel.

Of course, you don't have to disable both options--you can select one or the other, or both.

SPELLING BUTTON

I've been using Access since version 1, and I just found the Spelling button on the Table Datasheet toolbar. It's not hidden--it's right on the toolbar. Nor does it have an obscure design. It has the text ABC and a checkmark on it. What could be plainer? Simply select a cell, a field, or the entire table, then click the button. The Spell Checker feature will respond accordingly, just as if you'd selected Tools, Spelling.

USING SQL TO QUERY FOR THE FIRST N RECORDS

You probably know that you can use a query's Top property to return only a specific number of records, instead of all the records. For instance, after sorting all your orders by the total value, you may want to select only the top ten sales. To do so, you'd specify 10 as the query's Top property.

You can do the same thing with SQL using the TOP keyword in the form

SELECT TOP n field(s) FROM table

More than likely, you'll need to also include an ORDER BY clause. For instance, to select the top ten best sales from a table named tblSales, you might use a statement similar to the following:

SELECT TOP 10 * FROM tblSales ORDER BY TotalOrder

where TotalOrder is the name of the field with the total sales value for each order. If you don't specify an argument for ORDER BY, SQL defaults to ascending order.

When using the TOP keyword, don't confuse it with a value, as in the greatest or highest values. The keyword simply returns the first records in the query's results.

SQL DELETE WARNING

The SQL DELETE clause sometimes refers to a single column, but don't assume that field's value is the only one being deleted. The SQL DELETE clause deletes an entire record--it makes no difference if the clause references only a single field. For instance, the SQL statements

DELETE FROM tblMyStuff WHERE Flag = True;

and

DELETE FROM tblMyStuff.OrderID WHERE Flag = True;

both do the same thing--they both delete all the records in tblMyStuff where the Flag field contains the True value. The second statement won't delete just the value in the OrderID--it will delete the entire record.

AN AUTOMATIC SUBFORM

If you use the AutoForm and it adds a subform, you're probably seeing the table's subdatasheet. A subdatasheet displays related records in another table in Datasheet View (it's new to Access 2000). If you don't want the subform, you have two alternatives:

Modify the table's Subdatasheet Name property to [None].
Use the Form Wizard to create the form (instead of the AutoForm).

If you choose to adjust the table temporarily, open it in Design View and click the Properties button. Choose [None] from the Subdatasheet Name property field's drop-down list. After creating your form using the AutoForm, restore the property setting.

CAN'T SUM

Have you ever added a Sum function to a report's footer expecting to return a grand total of one of your fields? If you have, you found it didn't work. Instead of a total, your function returned an error. That's because the Sum function won't work in a report's footer. To work around this limitation, add your Sum function to some other section of your report and set its Visible property to No. Then, in the report's footer, add a text box and set its ControlSource to the name of the text box that contains your working Sum function. For instance, if you named the control that contains the Sum function txtTotal, you'd then add another text box to the report's footer and use the expression

=txtTotal

as that control's ControlSource property.

SYSTEM INFORMATION

There's a treasure chest of information available with just a few quick clicks, and you probably don't know a thing about it. From the Help menu, click About Microsoft Access. In the lower-right corner, click the System Info button to open the Microsoft System Information dialog box. Here, you'll find information about your system: hardware, components, and even the other applications you have installed. Take a look around--you may be surprised at what you'll learn about your system.

CREATING A TABLE ALIAS--PART 1 OF 2

A self-join relationship allows you to relate records in the same table. To do so, you simply add the same table to a query twice. However, when you do so, Access renames the second query by adding a 1 (or the appropriate consecutive value) to the title. For instance, if you add the table tblMyTable to a query twice, Access will rename the second table tblMyTable_1. You can accept this default name or change it.

To change the default name, simply right-click the field list in question. Then, select Properties from the context menu and enter a temporary name in the Alias field.

An alias helps you differentiate between tables in the query. However, assigning an alias doesn't modify the name of the underlying table.

CREATING A TABLE ALIAS--PART 2 OF 2

In our previous tip, we discussed assigning an alias to a table in a query. You might want to do so when working with copies of the same table in a self-join.

The Alias property also comes into good use when your tables have long names. That's because Access won't display the entire title in the field list's title bar. Assigning a short alias in the query makes each table easier to find.

CREATING A TABLE IN DATASHEET VIEW

You probably know that you can create a new table in Datasheet view by double-clicking the Create Table By Entering Data shortcut in the Database window. This approach will open a blank datasheet with ten generic fields. As you enter data, Access will do its best to determine the appropriate data type for each field.

You can help Access choose the correct data type by entering descriptive characters. For instance, if you want a field to be a Currency data type, be sure to preface your entry with your system's Currency symbol, such as the $ character. When entering percentage values, be sure to include the % symbol, and Access will assign the Number data type and Percent format. The more information you enter with your data, the more complete your table will be.

LISTING TABLE FIELDS

You can use VBA to create a list of fields, but if all you need is a list, you might consider using the Documenter. It's certainly a lot quicker and easier. Granted, the Documenter will also print a lot of other information, but you can limit most extraneous information.

To use the Documenter, select Tools, Analyze, Documenter. In the resulting dialog box, click the Tables tab, and then select the appropriate table. To limit the information that this feature prints about the table, click the Options button and choose the Names, Data Types, and Sizes options in the Include For Fields section. Then, click OK twice. Access will examine the specified table and create a report that lists that table's fields, as well as each field's data type and size.

ORDER IN THE TABLE

For the most part, the order of your table fields is irrelevant, because you can display data in any order you want. However, it's a good idea to position your fields in the same order you plan to arrange your data entry forms. If you use a form wizard to create your form, that wizard will rely on the field order to add controls. If your field order matches your data entry order, you won't have to rearrange anything.

A form wizard doesn't have this problem. If you use a form wizard instead of an auto wizard, you determine the order of your controls. Simply add fields in the order you want them to appear in your form.

CLEANING UP YOUR TABLES

You won't often want to delete a table, but it's an easy process when you do. Simply select the table in the Database window and press the Delete key, then click Yes when Access wants you to confirm the action. If you want to omit this action, press Shift-Delete, but be very careful when using this shortcut. In fact, always think twice before deleting a table full of data. You might want to archive the data instead of deleting it. However, if you do delete a table and change your mind, you can undo the delete by clicking the Undo button or choosing Undo from the Edit menu. But you must do so immediately after deleting the file--before you take any further action.

HIDING A TABLE

It's easy to hide a table so the average user doesn't know it exists. Right-click the table in the Database window, then choose Properties from the submenu. In the resulting Properties dialog box, click the Hidden option at the bottom of the box. Next, click Apply and then click OK.

When you're ready to work with the table, choose Tools, Options and click the View tab. Then, select Hidden Objects from the Show options and click Apply. Finally, click OK to return to the Database window, which will now display your hidden table.

CAN'T MODIFY TABLE

Most of the time, it's easy to modify a table. You open it in Design view, then make your changes and save them. If Access won't let you modify a table, there are a few possibilities. First, make sure the table's not open by pulling down the Window menu and checking the active objects. (VBA code could also be the problem. We discussed this possibility in an earlier tip named BOUND BEHAVIOR.)

If your table's not open, and you're working with a networked copy, you may have to wait until no one else is working in the same database. To make sure of this, choose Tools, Options and then click the Advanced tab. If the Shared button in the Default Open Mode section is selected, you must be the only one working in the open database. In other words, everyone else must close the appropriate .mdb file before you can make changes to a table.

When the Shared button isn't selected, you'll need to contact your network administrator. Chances are you don't have permission to modify tables.

QUICK TABLES

If you're running out of time and you need a new table, you can bypass Design View, go straight to an empty table, and start entering data. To do so, in the Database window click the Tables tab and then click New. Then, click Datasheet in the New Table dialog box.

Access will open a blank table with default field names. You can start entering your data right away. Later you'll probably need to go back and name fields and tweak field properties. But in a pinch, it's a real time-saver.

AN UNRELATED TABLE

Most multiple-table queries are based on related tables. If not, Access will create what's known as a cross, or Cartesian, product. That means Access joins every record in every table. For instance, if you add a ten-record table and a 20-record table, your query will have 200 records. It's not often that you'll want this kind of datasheet, but you might.

Let's suppose you want to match each of your products with every storage medium in your warehouse to learn the least expensive way to store your inventory. To do so, you'd join your product and storage medium tables without creating a relationship. The product would be a record for every product in every medium. At this point, you could compare all the mediums for each product to determine which is the least expensive. Of course, there'd be more calculations going on, but this simple example gives the gist of the arrangement.

EXPORTING TABLES

When you export a table, Access exports the records in the order you entered those records. Sorting the table makes no difference. If you need to export records in a specific order other than the table order, you must create a query to do your sorting. Then, export the query, not the table.

CONVERT TEXT TO NUMBERS

If you save numeric-based data as text when you don't need the entries for calculations, you can still use the data in calculations if necessary. Simply use the Val() function to return the numerical value of the data. You can use a query expression or a calculated control in a form or report to display the results.

However, the Val() function considers values from left to right only and stops when it encounters an alpha character. That means the Val() function won't return a value for entries that begin with alpha characters. In other words, the two entries 123abc and abc123 respond differently to the Val() function. 123abc returns the value 123, and abc123 returns nothing.

TEXT VERSUS MEMO

Technically, a Memo field stores text and accepts more characters than a Text field, so you might wonder just why you'd need to bother with a Text field. There are a few distinctions between the two, and knowing them will help you choose the most appropriate data type.

A Text field accepts up to 255 characters, including letters, numbers, and any punctuation. A Memo field can hold up to 64,000 characters. You can limit the size of a Text field using the Field Size property; you can't limit the size of a Memo field. Text fields accept formatting, but Memo fields don't.

Therefore, you'll want to rely on Text fields when the entry is relatively small (fewer than 255 characters) or you want to limit or format the data. When the entry is larger than 255 characters, use a Memo field.

WORKING WITH ELAPSED TIME

One of the questions we see most often is how to track time when the time period begins in one day and extends into the next (past midnight). Fortunately, there's an easy way. But first, let's look at a simple expression that tracks time within the same 24-hour period (midnight to midnight). If you know that the time period will be inclusive of the same day, you can use an expression in the form

end - start

where end marks the ending of the time period and start marks the beginning of the time period.

Unfortunately, this expression doesn't work with elapsed time. In these cases, you'll need the expression

1 + end - start

PROTECTING TOOLBARS AND MENU BARS

You can easily change a toolbar by choosing View, Toolbars, Customize. When you do, Access opens all toolbars and menu bars in edit mode. While this makes it easy to modify a toolbar, it also makes accidents more likely--if you're not careful. With all the toolbars and menu bars in edit mode, you could easily make a change to the wrong toolbar without realizing it at the time. For this reason, we recommend you close all toolbars and menu bars, except the one you mean to change.

To close a toolbar or menu bar, select View, Toolbars, Customize. In the Customize dialog box, click the Toolbars tab and deselect the toolbar or menu bar in the Toolbars control.

RESETTING TOOLBARS

If you make changes to the built-in toolbars, you can always reset them to their default condition. To do so, right-click the toolbar and choose Customize from the resulting shortcut menu. Next, click the Reset button in the Customize dialog box. Just keep in mind that you're not deleting only the most recent change--you're returning the toolbar to its original settings when you installed Access (or Office).

SUBMENUS AS TOOLBARS

Did you know that some submenus could double as toolbars? They can, and this capability is especially convenient if you find yourself using one of these tools repeatedly because you'll omit a few keystrokes.

For instance, let's suppose you use the Font Color tool often. You click the arrow to the right of the Font Color button and choose a color--each time you use the tool, you must open it first. You might find working with this tool as a floating toolbar a bit easier.

To do so, open the control's drop-down list and position the cursor over the horizontal gray bar at the top of the list. In response, the bar will turn blue, which means you can drag this control to a floating position. (This behavior is true of any tool that can double as a floating toolbar.) Simply click the blue bar and drag the control to a position off the toolbar. To close the new toolbar, click the Windows Close button (the X button in the top-right corner).

DOCKING THE TOOLBOX

The Toolbox is actually a toolbar. As such, you can dock it or move it the same way you do any other toolbar. If you prefer a floating Toolbox, you can still move it by clicking its title bar and dragging the Toolbox to a new position.

If you decide to dock the Toolbox, simply double-click the title bar, and Access will dock it below the other docked toolbars. To undock the Toolbox, grab the handle at the toolbar's left border and then drag the Toolbox back to the Access window.

CAN'T USE TOP VALUES WITH DELETE

You might not realize it, but you can't use the Top Values property in a Delete query. For instance, let's suppose you wanted to delete 10 percent of your records. You might try setting a Delete query's Top Values property to 10%, but you'd find the Top Values property missing from the property sheet.

A reader has a solution to this limitation. Create a Select query using the Top Values property. Then, base the Delete query on the Top Values Select query. When you run the Delete query, it will delete the appropriate number of records from the Select query's underlying table or data source.

A TOTAL ERROR

Ever have a Totals query return the error

Cannot group on fields selected with '*"

If so, the query's Output All Fields property is probably set to Yes--and that just won't work. You see, a Totals query groups the fields by entries, so you need to be careful about the fields you add to a Totals query. Each additional field will increase the number of "groups." When the query's Output All Fields property is set to Yes, then your query is considering all the fields in all the underlying data sources. The effect would be a query that's not grouped at all, since each field is a "group."

Fortunately, the solution is simple. Open the query's properties sheet by clicking the Properties button and set the Output All Fields property to No.

CAN'T UNDO

You're probably familiar with the Undo button. In fact, we've written a few tips about this feature, which allows you to undo many tasks. Unlike some of the other Office programs, Access lets you undo some tasks after saving a table. For instance, try opening any table and changing the contents of any cell. Then, save the table. Notice that the Undo button is still enabled. If you click it, Access will undo your change, even though you've saved the table.

FASTER UPDATE

You can use a combo box to update other form controls. For instance, if you choose an employee by name from a combo box, you might then use a DLookup() function to return that employee's address and phone number. If so, there's a quicker way. If all the data you want to display is stored in the same record, try using the Column property in the form

ctrl.Column(x)

where ctrl is a reference to the combo box and x identifies the position of the corresponding field in the combo box. Just remember to add all the necessary fields to the combo box when you're creating it. In addition, x is 0 based, so the first field in the combo box equals 0, the second equals 1, and so on.

WORKING IN THE VISUAL BASIC EDITOR

Sometimes when you're changing properties using VBA code, the setting isn't obvious. Fortunately, the Visual Basic Editor displays the active object's properties in the lower-left corner. When you need to know a setting, return to the form or report in Design view and apply that setting. Then, return to the Visual Basic Editor and view the modified object's properties in the properties window. This method is particularly useful when you don't know the correct syntax for the property.

Let's consider a simple example. Suppose you want to display a control's text in bold print and you need to know the integer value that represents the bold setting. (We recommend using intrinsic constants when available.) First, you click the View Microsoft Access button on the editor's Standard toolbar. Then, you change the control's Font Weight property to Bold in the property sheet. When you return to the Visual Basic Editor, review the properties in the property window for the FontWeight property setting, which should be 700. Now you know the integer value, and you can complete your code statement. Just don't forget to return to the form and change the object's property back to its original setting.

TURNING OFF WARNINGS

When you run an action query, Access will display a message warning you that you are about to modify the existing data. If you don't want to display these warnings, you can temporarily turn them off using the SetWarnings method. The statement

DoCmd.SetWarnings False

turns off the display. Substitute the False value with True to turn on the display.

However, we recommend that you not add the SetWarnings statement until the procedure is complete, debugged, and running as expected. During the testing and debugging process, those messages can give clues as to whether the code is running as expected.

RESIZING THE WEB BROWSER CONTROL

In our previous tip, we told you about the Microsoft Web Browser control, which you may have trouble resizing. Thanks to a reader for sharing his innovative workaround for this apparent bug. If you can't resize your Web Browser control, select it and press Ctrl-X to cut the control from the form. Next, press Ctrl-V to copy it back to the form. Now, resize your control and save your form.

No one seems to understand why this simple cut-and-paste task does the trick, but it definitely does. We've been wanting to share the Microsoft Web Browser with our readers for months but didn't because we couldn't resize the darn thing. Thanks!

FORCING A WEEKDAY

Limiting the type of data you enter doesn't always require a complex event procedure. Sometimes, the Validation Rule property is one way to limit the type of data you enter. For instance, if you want a control to accept only weekday values, you can enter the expression

DatePart("w",[field]) Mod 7 >= 2

as the control's Validation Rule property.

Saturday and Sunday will return the integers 0 and 1, respectively. Consequently, checking for a value that's greater than or equal to 2 eliminates any entry that equals 0 or 1. Therefore, the control won't accept any date that falls on a Saturday or Sunday.

AN IMMEDIATE WINDOW SHORTCUT

When you want to run an expression, function, or variable in the Debug window, you don't have to retype it. You can drag it from its module to the Debug window and then run it. You may need to revamp it just a bit by adding a ? character to the beginning of the statement or adding a passed argument. But you'll save yourself a little time and cut down on typos.

ORDER IN THE WIZARD

When working with wizards, you generally select fields from an Available list box and move them to a Selected list box. The wizard then uses those fields to create a form, report, or query--depending on the type of wizard you're running.

You may have noticed that the wizard displays the fields in the same order as they occur in the underlying table or query. If this order doesn't match the order in which you want these fields to appear in the finished object, you're not stuck. You don't have to rearrange all your controls after the fact.

The order in which you select fields in the Available list determines the order in which the wizard displays them in the resulting form, report, or query. Simply select the fields in the Available Fields list box in the order you want for your finished form or report.

CREATING A SUBTOTAL REPORT

Typically, grouped reports beg for subtotals, but the report wizard won't just give them to you--you'll have to work for them. Here's how:

Create your report--it needs to be a grouped report.
Open the group footer section and enter the appropriate subtotal function. For instance, if you're wanting to display a total of each region's sales, your function might be similar to =Sum([Sales]).

The results will be an additional line below each group displaying the result of your function. That result will reflect only the records in its group.

SEE YOUR REPORT IN WORD

At some time or another, you'll probably want to export a report to Word so you can take advantage of Word's formatting capabilities. Fortunately, doing so is easy: Open your report or select it in the Database window. Then, locate the Office Links tool on the Database toolbar and open its drop-down list. Finally, choose Publish It With MS Word from the drop-down list.

Access will open Word along with an RTF file of your report. At this point, you can work with your report as you would any other Word document.

ZOOMING IN

Quite often, you can't see an entire expression or function in a property field. When this happens, press Shift-F2 to open the expression or function in the Zoom window--a large dialog box that will more than accommodate your expression or function. So the next time you're working with a long expression, remember the Zoom window!

ZOOMING IN

When entering expressions in the query design grid, you can press Shift-F2 to open the Zoom dialog box. Doing so allows you to see the entire expression as you enter it or while modifying an existing expression. If you prefer the mouse, you can still zoom in on your expressions without using the keyboard. Simply right-click the cell that contains the expression (or will contain the expression) and choose Zoom from the context menu.

LEADING ZEROS

There are several solutions for adding leading zeros to a value, and most of them are more convoluted than they need to be. One of the simplest methods for adding leading zeros that we've found isn't all that intuitive, but it's simple and it works. In a nutshell, you add the value to a 10-based number that's one place larger than the number of characters you need for each entry. For instance, if you want all values to have five characters, using leading zeros to fill in as needed, you'd use the number 100000--that's one place more than five. The function

Right(value + 100000, 5)

will return five characters from the result of adding value to 100000--including leading zeros. If value equals 30, the function will return 00030; a value of 4321 will return 04321, and so on.