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


The information in this article applies to:

  • Microsoft Access 2000
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.

  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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbhowto kbinfo kbusage KB210234
©2004 Microsoft Corporation. All rights reserved.