Report Control Criteria

Topic

New Reply

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.

Reply | Quote

Replies

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 http://support.microsoft.com/support/kb/ar…s/Q231/8/51.ASP, but you’ll have to hunt through the knowledge base for the self-extracting 97 file, which is called RptSmp97.exe.

Reply | Quote

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.
eg

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.
eg

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Field1Property1Change
End Sub

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

Reply | Quote