ACC: How to Flag Out-of-Sequence Numbers on a Report

ID: Q137118

The information in this article applies to:

• Microsoft Access 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a report that marks (or flags) any items that are missing from a given a list of sequential integers or dates. For example, when you receive your bank statement, your bank has flagged checks that have not cleared by placing an asterisk by the next check in the sequence. This article uses a Visual Basic for Applications function to compare the current item with the previous one in the sequence.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

To create a function that compares the current item with the previous one in a sequence, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

 1. Open the sample database Northwind.mdb.

 2. Create a new report based on the Current Product List query.

 3. On the View menu, click Code.

 4. Type the following code in the module's Declarations section:

       Dim GlobalFlag as Variant

 5. On the Insert menu, click Procedure. Type "SetFlag" (without the
    quotation marks) in the Name box, and then click OK.

 6. Modify the SetFlag function as follows:

       Function SetFlag(x as Variant)
          If GlobalFlag <> x - 1 then
             SetFlag = "*"
          Else
             SetFlag = ""
          End If

          GlobalFlag = x
       End Function

 7. Close the Code window.

 8. On the View menu, click Sorting and Grouping to display the Sorting
    and Grouping dialog box.

 9. In the first row of the Sorting and Grouping dialog box, select
    ProductID for the Field/Expression column and select Ascending for
    the Sort Order column.

11. Add the following three text box controls to the detail section of the

    report:

       Textbox:
          Name: Product ID
          ControlSource: ProductID
       Textbox:
          Name: OutOfSequence
          ControlSource: =SetFlag([ProductID])
       Textbox:
          Name: Product Name
          ControlSource: ProductName

    OutOfSequence control is flush against the Product ID control.

    Preview window, note that an asterisk is placed next to each
    ProductID where the preceding ProductID is not in sequence.

Keywords          : kbusage RptOthr 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998