Microsoft KB Archive/137118

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

Article ID: 137118

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q137118





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.  Open the sample database Northwind.mdb. Create a new report based on the Current Product List query. On the View menu, click Code.  Type the following code in the module's Declarations section: Dim GlobalFlag as Variant  On the Insert menu, click Procedure. Type SetFlag in the Name box, and then click OK.  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 </li> Close the Code window.</li> On the View menu, click Sorting and Grouping to display the Sorting and Grouping dialog box.</li> 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.</li> Close the dialog box.</li>  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 </li> Place the controls so that they are aligned horizontally and that the OutOfSequence control is flush against the Product ID control.</li> To view the results, click Print Preview on the File menu. In the Preview window, note that an asterisk is placed next to each ProductID where the preceding ProductID is not in sequence.</li></ol>

Keywords: kbhowto kbprogramming kbusage KB137118

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.