Training Guides

Microsoft Tips

Mobile Devices


Related Links


Access Tips

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.

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.

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.

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.