Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Report Control Criteria

    I have a Report which is used to track "open" and "overdue" projects. The underlying Query Criteria is based on measuring the "Actual Close Date" against the "Target Date". It only returns records based on if the "Actual Close Date" is open (Null), or if it past the "Target Date". Here is the criteria I defined in the query:

    Is Null Or >[TargetDate]

    The Report works fine, however the Users of the Report want it more, shall I say, colorful. They want the Open (null), or Closed past the defined Target Date to "stand out" in the report. The idea is either make the control Border thickness greater, such as 2 or 3, and/or make it colored, such as red, for printed copies.

    I have tried to place the same sort of coding as mentioned above in the OnOpen, and OnActive properties of the Report, along with code to change the Border weight and color. Naturally I am asking for assistance since it does not work.

    Am I traveling down the right path here, or do Reports act differently then say Forms? I thought of using a Form for a report, but it is not going to meet the sorting & grouping requirements I need.

    As always, thank you in advance for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Report Control Criteria

    Yes, reports are very different from forms. Conditional formatting has to be done on a record by record basis, which usually means the code has to go in the Format event of the detail section. You can download sample databases from the Microsoft site for both Access 97 and 2000 reports. The Access 2000 file can be downloaded from <A target="_blank" HREF=></A>, but you'll have to hunt through the knowledge base for the self-extracting 97 file, which is called RptSmp97.exe.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Brisbane, Queensland, Australia
    Thanked 0 Times in 0 Posts

    Re: Report Control Criteria

    Good question and equally as good answer. I have been looking for this hint for a quite some time now. Thanks Charlotte!

    I didn't look in the MS knowledge base or download the file but I used the following method:

    Open the report in design view
    Open the properties sheet for the Detail section
    Select the "On Format" Event
    Select the elipses (...)
    Select Macro Builder
    View Conditions
    Create condition eg [Field1]>0
    Use Setvalue to set the control property eg Setvalue Item:[Field1].[Property1] Expression:Expression1
    Close & Save the macro
    Save the report
    View the report

    It turns out that once you change a property it remains in that condition for the rest of the fields in the report. To prevent this you have to set the property to a default value and then set it for the condition.
    <pre>Condition Action
    Setvalue Item:[Field1].[Property1] Expression: Expression1
    Condition1 Setvalue Item:[Field1].[Property1] Expression: Expression2

    This takes two lines in the macro. The first condition is left blank to enable the value change every time the macro runs thus resetting your default.

    Hint1: You could write code to do this but it is much easier to write a macro, especially if you are relatively new to Access.

    Hint2: Code runs faster than a macro so now you can convert your macro to code (procedure) and delete the macro. The method is:

    View the macros of your database
    Right click the macro you created
    Select Save As/Export..
    Select Save as Visual Basic Module
    Allow Error handling and comments
    Click Convert

    Heh presto! You now have a module with a similar name to that of the macro. You can now copy the procedure out of this module and into your other modules or leave it as it is (at least change the name to something sensible). Don't forget to change the "On Format" event of the Details section of your Report to "Event Procedure" and call your new procedure from within this event procedure.
    <pre>Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    End Sub</pre>

    This has the advantage of being able to call your procedure from any report or event.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts