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

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.

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.

1. Start Access and then open the sample database Northwind.mdb.
2. Create a new report based on the Current Product List query.
3. On the View menu, click Design View.
4. Return to the View menu and click Code.
5. 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
   						

6. On the File menu, click Close and Return to Microsoft Access.
7. On the View menu, click Sorting and Grouping to display the Sorting and Grouping dialog box.
8. 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.
9. Close the Sorting and Grouping dialog box.
10. Add the following three text box controls to the detail section of the report:

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

11. Place the controls so that they are aligned horizontally and so that the OutOfSequence control is flush against the right of the ProductID control.
12. 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.

For more information about creating reports, click Microsoft Access Help on the Help menu, type create a report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about adding a text box control to a report, click Microsoft Access Help on the Help menu, type create a text box or other type of control on a report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.