Grouping months in a Totals query

If you've ever tried to group date values in a Totals query, you probably used something similar to the Format() function to group by month and year, or perhaps even just the Month() function. Both of these pose problems, however. The Format() function returns a String value, not a date. This makes it difficult to sort the results in any meaningful way without resorting to odd date formats, like 199808 or something of the sort. Of course, the Month() function returns a numeric value, but what happens if
your data extends over a period of years? Using this function places all records of the same month into the same group regardless of the year. A better alternative for grouping dates by month is to use the DateSerial() function. This function follows the syntax

DateSerial(Year, Month, Day)

and returns a Date value. To group by month, simply change the Day argument to a 1. For example, the expression in your query field might look similar to this one

MyMonthGroup:DateSerial(Year([TableDateField]), Month([TableDateField]),1).

Using this expression, 8/3/98 and 8/14/98 would both be grouped as 8/1/98.

Size To Fit Form option

The Size To Fit Form option on the Window menu automatically adjusts the size of an open form to fit the current record. Depending on the form's Default View (Single or Continuous) this option behaves differently.

For Single Forms, Access shrinks or expands the window accordingly to display the entire record. For Continuous forms, if only part of the bottom record is showing, Access shrinks the form to encompass the previous record. If only part of one record is displayed, then the form lengthens to show as much of that record as possible. In all cases, the form widens or narrows to accommodate the record's width.

Viewing Access reports without Access

There are three main ways to let others without Access view Access reports.

1. You can export the report to Word.  To do so, select the report, then choose the Publish to MS Word option from the Office Links dropdown menu.
    Doing so, however, changes the report to Rich Text Format, which strips out any graphics or lines that you?ve used to format the page.

2. Save the report as HTML and then view it with a web browser.  From the Database window, select the report and choose File/Save As HTML from the menu bar.
    The Publish to the Web Wizard will lead you through the steps necessary to create the web page.  After you've finished, launch your web browser.

Netscape users should select File/Open Page from the menu bar, then click the Choose File button. Internet Explorer users choose File/Open, then click the Browse button.

You can then search for the saved HTML report on your computer. This method also removes lines and graphics from the layout, and creates a separate file for each report page.

3. Save the report in Snapshot Format, which maintains the graphical layout and shows multiple pages.  To do so, select the report in the Database Window, then choose       Save/Export from the File menu.  Access displays the Save As dialog box.  Click OK to save To An External File Or Database. When the next Save As box appears,      change the Save As Type from Microsoft Access to Snapshot Format, select a location to save the report, then click the Export button.  To view the file, you'll need the      Snapshot Viewer, which you can download for free from

Using ListWidth property

A combo box's ListWidth property determines how wide the dropdown list box portion will be. While the list can be set wider than the actual combo box,
it can't be narrower. The default setting, Auto, extends the width to match the combo box itself. To make the list box wider, enter a number in inches
or centemeters into the ListWidth property field. Similarly, the List Rows property determines the maximum number of rows to display in the dropdown
list box. This value can be any integer between 1 and 255.

Proper case with the StrConv() function

When it comes time to print a report, you can't always be sure the text in your database will be formatted correctly. Input errors are to be expected, but even so, a customer letter that starts with "Dear Mr. lARRY jOHANSEN" can be a problem. Fortunately, Access provides a way to accomodate such potential embarrassments with its string conversion function, called StrConv(). With it, you can reformat text three different ways: all uppercase, all lowercase, and proper case--the first letter of every word is capitalized. This function takes the following form:

StrConv(string, conversion)

The string argument can be a literal string, such as "lARRY jOHANSEN", or more likely, the field that contains the questionable text, [FullName], for example. Conversion is one of three VBA constants, vbUpperCase, vbLowerCase, vbProperCase. To use this function on a report or form, place it in the Control Source of a text box.

=StrConv([FullName],vbProperCase)
would display "Larry Johansen".

Setting your forms' default appearance by creating a new template (All versions)

Once you've designed an attractive form, you can specify that look as your form default and save yourself a lot of design time. To do so, you simply change the template that Access uses when you create a form without relying on a form wizard.

First, pull down the Options... menu and click the Forms/Reports tab (or choose the View menu in version 2.0). By default, the Forms Template text
box contains the reference to the Normal template. Simply replace this with the name of your form, and then click OK. If you're using version 2.0,
highlight Form Report Design in the Category list box. Then, in the Items box, select the word Normal in the Form Template text box and type the name
of the new form. Finally, click OK.

Quickly displaying a list of constants (Access 95 and Access 97)

To display a list of constants, simply open any module (including a blank one) in Design View and press [Ctrl]J. Access will respond by displaying a
scrollable list of constants. You can also press [F2] to display the Object Browser and then choose the appropriate library.

Indexes aren't what they're cracked up to be

Although you can use indexes to sort data, we don't recommend that you rely on this technique because indexes don't always work as you expect. Access uses an index to locate data more efficiently using its internal logic and rules. Therefore, Access may ignore your index. Furthermore, an index
that's contrary to the internal rules Access follows can actually slow down your application.

Two easy ways to switch a control's Enabled property

You can enable or disable most controls by modifying the control's Enabled property. You can take advantage of that property's Boolean value settings
by using a statement in the form

control.Enabled = Abs(control.Enabled) - 1

where control is the name of the object you're modifying. This statement simply switches the property between the two Boolean values, -1 and 0; -1
is enabled, and 0 is disabled.

You can also use the Not operator to switch a control's Enabled property. This technique uses True and False values instead of the Boolean values -1
and 0. The setup is basically the same, but the code is a little different. The Not technique uses a statement in the form

control.Enabled = Not control.Enabled

If the Enabled property is True, this statement changes it to False and vice versa.

Quickly copying data

When copying table data from Access to Excel, select the entire Access table by clicking the gray square at the top-left corner of the table and press [Ctrl]C. Then, switch to Excel, select the top-left cell of the range you want to copy the data to, pull down the Edit menu, and choose the Paste
Special... command. (Don't press [Ctrl]V to paste the data, or Excel will truncate the text.)

To copy Excel data to Access, highlight the range you want to copy and press[Ctrl]C. Then, switch to Access, open a blank table, select the entire
table by clicking the gray square at the top-left corner of the table, press [Ctrl]V, and click Yes. If you want to append records to an existing table, open that table, select it, pull down the Edit menu, choose Paste Append, and click Yes.

Efficient parameter queries

If you want to prompt users for more than one query parameter, you might think you need more than one criteria expression, but you don't. For instance, if you need two parameters--a beginning and ending date--you don't have to enter two criteria expressions in the form

>=[Enter Beginning Date]
<=[Enter Ending Date]

Instead, you can combine both in the same expression using the form

Between [Enter Beginning Date] And [Enter Ending Date]

Using Format() with dates

You can use the Format() function to display different date components. For instance, to return a date's day, month, or year, you'd use the functions

Format([datefield],"d")
Format([datefield],"m")
Format([datefield],"y")

respectively. To display the month, you can use the form

Format([datefield],"mmm")

to return the abbreviated month or the function

Format([datefield],"mmmm")

to return the month's full name.

You can also combine the formats to create unique formats, as in

Format([datefield],"dmmmyy")

which would display the day, abbreviated month, and a two-digit year value, with no spaces in between each component.

Coloring a command button

You can't change the color of a command button, but you can make it look like you have by following these steps:

1. Add a command button to your form.
2. Set the button's Transparent property to Yes.
3. Create a rectangle the same size as your button, and use the Fill/Back
Color tool (or the Palette tool in version 2.0) to choose a color.
4. Set the rectangle's Special Effect property to Raised.
5. Drag the command button over the rectangle.

Having a rectangle under the command button won't affect its performance.
However, setting its Transparent property to Yes inhibits any caption you might add. You can work around this though by adding a label control to the
top of the two-tiered button.

Efficient queries

If you nest queries, you might want to rethink your strategy. You can refer to the results of one query expression in the same query. For instance, if your query contains the expression

Total1: [field1] + [field2]

you might run a second query and use the expression

Total2: [Total1] * 5

Instead of running a second query, simply add the second expression to the first query.

Printing relationships

There are two ways to print a graphical representation of your database's relationships. First, if you're using Access 97, you can download the Print
Relationships Wizard-a free add-in from Microsoft. To download this add-in, visit

www.microsoft.com/accessdev/articles/printwiz/default.htm

and follow the instructions given-be sure to note where your system stores the file (our system stored the file in the Windows\System directory). Once
you've downloaded the add-in, locate the installation file named Prels80.exe and double-click it to start the installation process.

After installing the wizard, launch Access 97, pull down the Tools menu, select Add-Ins, and choose Print Relationships from the resulting submenu.
The wizard will create a report that shows a graphical representation of the relationships that exist between the tables in the current database.

The second method will work with version 2.0 and Access 95. It requires a few more steps than the wizard:

* In 2.0, pull down the Edit menu and choose Relationships...; in 95, select Tools/Relationships.
* Select the appropriate tables, queries, or both in the Add Table dialog box, clicking Add after each selection, and then close the dialog box (you use the  Show Table dialog box in 95)
* Press [Alt][PrintScreen]
* Open WordPad
* Press [Ctrl]V
* Print

Using pop-up forms (all versions)

Pop-up forms are great for displaying additional information about the current record or for requesting more information from the user. There are two kinds of pop-up forms: modeless and modal. A modeless pop-up form, which you use to display additional information, allows users to access other objects and menu commands while it's open. A modal pop-up form, which you use to request additional information, forces a user to attend to its task before he or she can continue to work.

To set up a modeless pop-up form, first restrict data entry by setting the Allow Edits, Allow Deletions, Allow Additions, and Data Entry properties.
(Version 2.0 users should set the Default Editing property to Read Only.) Then, set the Pop Up property to Yes and the Modal property to No.

A modal pop-up form requires some data entry-so you should set the Allow Additions and Data Entry properties to Yes. Set the remaining data entry
properties (listed above) to No. (Version 2.0 users should set the Default Editing property to Data Entry.) In addition, set the Pop Up and Modal
properties to Yes.

Regardless of the mode, setting the Border Style property to Dialog will enhance the form's display. Doing so also inhibits the Maximize and Minimize buttons.

Determining if the current record is a new record (all version)

Access 95 introduced a form property, New Record, which returns a True or False value. If the active record is new (meaning unsaved), New Record
returns True; otherwise, it returns False. This property makes it easy to alert your users that they're working with a new and unsaved record. To use
the property, just attach the following procedure to your form:

Private Sub Form_Current ()
If Me.NewRecord Then MsgBox "This is a _ new record."
End Sub

Inhibiting the system error message (all versions)

You can inhibit system error messages by setting the system's error state to 0. You do so by adding the statement

Response = acDataErrContinue

to event procedures that pass a Response integer. (The version 2.0 constant is DATA_ERRCONTINUE.)

Working with the Apply Filter event (Access 95 and Access 97)

By using the Apply Filter event, you can instruct VBA to display custom messages during a filtering task. Access executes this event when you click
the Filter by Selection button or choose the Apply Filter/Sort option from the Records menu. (Check the Help section for a more complete list.) You'll
probably use the Apply Filter event to check or change the current filter, but you can also use it to restrict the filter to certain fields. The
following code will display the current filter and allow you to continue or cancel:

Private Sub From_ApplyFilter(Cancel As _ Integer, ApplyType As Integer

Dim strMessage As String, intResponse As _ Integer

If ApplyType = acApplyFilter Then
strMessage = "The current search criteria _is:"
strMessage = strMessage & vbCrLf & _Me.Filter
intResponse = MsgBox(strMessage, _vbOKCancel + vbQuestion)
If intResponse = vbCancel Then Cancel = _True
End If
End Sub

Returning the last date in a group (all versions)

You can use a Totals query to group your records and then set the group aggregate to First or Last to determine the first or last record in the group. Of course, the First or Last aggregate field must be a Date field.

Filling a combo box with a dynamic list of objects (Access 95 and Access 97)

It's easy to offer your users a list of combo box items that updates itself automatically. For instance, suppose you want to fill a combo box with a list of all the reports in the active database and you want Access to update the list if you add reports to or delete reports from the database.
To do so, you'd use the following code:

Private sub cboReport_GotFocus()

Dim db AS Database, cnt As Container, doc _As Document
Dim strTemp As String, strList As String

Set db = CurrentDB
Set cnt = db.Containers!Reports

For Each doc in cnt.Documents
strTemp = doc.Name
strList = strList & strTemp & ";"
Next doc

Me!cboReport.RowSource = strList

Set db = Nothing
Set cnt = Nothing
Set doc = Nothing

In addition, be sure to set the combo box's Row Source Type property to Value List.

If you want to fill the control with a list of forms instead of reports, replace the Set cnt = db.Containers!Reports statement with the statement

Set cnt = db.Containers!Forms

Creating a table of error codes and their descriptions

Access has a ready-to-use function in the Help section that will create a table of error codes and their descriptions. If you're using version 2.0, open the Help menu, choose Search..., enter error codes, and click Display.
In the resulting window, click Determining Used Error Codes at the top of the page. Then, highlight just the code portion of the help information and
click the Copy button. Return to your database, open a blank module, and press [Ctrl]V to paste the code into your module. Replace the Sub keyword
in the function's title statement with the Function keyword and then click the Compile Loaded Modules button. To run the function, open the Immediate
window and type

? CreateErrorsTable()

The procedure will take a moment to run and prompt you when it's done. Just click OK to create the table Errors Table.

If you're running Access 95, the process is basically the same, except you'll find the function in the Help section under the Visual Basic topic.
Within this topic, you'll need to find the subheading for error handling and click Display. Next, double-click Determining the Error Codes Reserved
by Visual Basic. At this point, highlight just the code and continue as discussed above until you're ready to run the procedure. Then, press [Ctrl]G to open the Debug window (rather than the Immediate window).

If you're running Access 97, you'll find the function, which is called AccessAndJetErrorsTable(), in the Help section under error codes. Once you
locate the function, simply cut and paste it to a module and then run it via the Debug window.

Displaying a form (Access 95 and 97)

When your form is in Design View, you can get a quick glimpse of your work by pressing [F5]. Doing so displays your form in Form View without your
having to return to the Database window.

Adding multiple controls of the same type (Access 95 and 97)

In Form View, you choose a control from the Toolbox to add a control of the selected type to a form. If you want to add more than one control of the
same type, double-click the appropriate button on the Toolbox. Then, you can add as many controls to your form as you like without re-clicking the
Toolbox. When you finish, simply click the Toolbox to reset it.

Referencing subforms

Access treats subforms as controls. So, when referencing them, the proper syntax is

Forms![formname]![controlname]

When referencing a control on a subform, you use the Form identifier in the form

Forms![formname]![controlname].Form![subformcontrolname]

Querying for random records

To query your tables for random records, first base a Select query on your table and include in the QBE grid the fields you want. Then, add an
expression in the form

RandomEx: Rnd([valuefield])

where valuefield is any field in the query that contains values-that field can be an AutoNumber, Number, or Date/Time field. Next, apply a sort order
to the RandomEx field-it doesn't matter which. When you run the query, the RandomEx field will return random values for each record. Access will then
sort the records based on those values. While the values will be sorted, the records themselves will appear to be random. To return only a percentage of random records, you can set the query's Top Values property to the appropriate percentage.

Legal Disclaimer
THE INFORMATION IN THIS PUBLICATION IS PROVIDED "AS IS". WE EXPRESSLY DISCLAIMS ALL REPRESENTATIONS AND WARRANTIES OF ANY KIND REGARDING THE CONTENTS OR USE OF THE INFORMATION INCLUDING, BUT NOT LIMITED TO, EXPRESS AND IMPLIED WARRANTIES OF ACCURACY, COMPLETENESS, MERCHANTABILITY, FITNESS FOR A PARTICULAR USE, OR NON-INFRINGEMENT. IN NO EVENT SHALL WE BE LIABLE FOR ANY DIRECT, INDIRECT, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, LOST BUSINESS OR LOST DATA, RESULTING FROM THE USE OR RELIANCE UPON THE INFORMATION, EVEN IF WE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU.