Microsoft KB Archive/210234

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

Article ID: 210234

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210234



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
This shows you how to create a report that marks (or flags) any missing items from a given a list of sequential integers or dates. For example, on your bank statement, you may notice that all checks that have not cleared are flagged with 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.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create a function that compares the current item with the previous one in a sequence, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Start Access and then open the sample database Northwind.mdb. Create a new report based on the Current Product List query. On the View menu, click Design View. Return to the View menu and click Code.  Type or paste the following code into the module: Dim GlobalFlag as Variant

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

GlobalFlag = x End Function  On the File menu, click Close and Return to Microsoft Access.</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, click ProductID in the Field/Expression column, and click Ascending in the Sort Order column.</li> Close the Sorting and Grouping dialog box.</li>  Add the following three text box controls to the detail section of the report: <pre class="fixed_text">      Textbox: Name: Product ID      ControlSource: ProductID

Textbox: Name: OutOfSequence Text Align: Left ControlSource: =SetFlag([ProductID])

Textbox: Name: Product Name ControlSource: ProductName </li> Place the controls so that they are aligned horizontally and so that the OutOfSequence control is flush against the right of the ProductID 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 or is missing.</li></ol>

<div class="references_section">