Criteria formatting

  • Thread starter Thread starter Max
  • Start date Start date
M

Max

Hello,

I have a report that shows outstanding issues. There is a
field that calculates the due days of an item. I would
like to italicize the whole record/line if the days are
positive. (+) means the item is outstanding (-) means
there is still time left.

Any assistance on code would be greatly appreciated.

Thanks
Max
 
Max,

If you are using Access 2000 or later, you can use Conditional Formatting.
Assuming the DaysLeft field holds the number of days left, then for each
control that you want to set to italics define conditional formatting as:
Condition 1 = "Expression Is" ... "[DaysLeft] > 0". Then click the Italics
button for the Condition 1 format.

For Access 97 and earlier, and assuming you want to italicize all the fields
in your detail section, then add the following code to your detail section's
Format event:

------------------------------
Dim ctl As Control
Dim blnSetItalics As Boolean

blnSetItalics = (Me.DaysLeft > 0)

For Each ctl In Me.Section(acDetail).Controls
ctl.FontItalic = blnSetItalics
Next ctl
------------------------------

The FontItalic only applies to certain types of controls (see help for
FontItalic) if you try setting it for illegal controls you will get an
error. If you want to set only some controls to italics, you could: Check
the control's ControlType property (see help) before setting FontItalic;
Call On Error Resume Next before setting FontItalic to just ignore any
error; or use the Tag property to identify the controls you want to
manipulate, as in the example below...

------------------------------
Dim ctl As Control
Dim blnSetItalics As Boolean

blnSetItalics = (Me.DaysLeft > 0)

For Each ctl In Me.Section(acDetail).Controls
If ctl.Tag = "Italics" Then ctl.FontItalic = blnSetItalics
Next ctl
 
Max said:
I have a report that shows outstanding issues. There is a
field that calculates the due days of an item. I would
like to italicize the whole record/line if the days are
positive. (+) means the item is outstanding (-) means
there is still time left.
Click to expand...


You need to set the property for each control in the
section.

Dim bolLate As Boolean
bolLate = (Me.thelatefield > 0)
Me.controlA.FontItalic = bolLate
Me.controlB.FontItalic = bolLate
. . .
Me.controlN.FontItalic = bolLate
 
While I like your last code example Lee, watch out when
using a section's Controls collection in A97 on a NT,
Win2000 or XP machine. There is a slight incompatability
between Access and those systems where a section's Controls
collection doesn't work. There's a KB article about
Me.Section(N).Controls.Count always being 0. It doesn't go
into other anomolous behavior, but there are other aspects
that fail as well.

A workaround is:

For Each ctl In Me.Controls
If ctl.Section = acDetail Then
If ctl.Tag = "Italics" Then
ctl.FontItalic = blnSetItalics




Lee said:
If you are using Access 2000 or later, you can use Conditional Formatting.
Assuming the DaysLeft field holds the number of days left, then for each
control that you want to set to italics define conditional formatting as:
Condition 1 = "Expression Is" ... "[DaysLeft] > 0". Then click the Italics
button for the Condition 1 format.

For Access 97 and earlier, and assuming you want to italicize all the fields
in your detail section, then add the following code to your detail section's
Format event:

------------------------------
Dim ctl As Control
Dim blnSetItalics As Boolean

blnSetItalics = (Me.DaysLeft > 0)

For Each ctl In Me.Section(acDetail).Controls
ctl.FontItalic = blnSetItalics
Next ctl
------------------------------

The FontItalic only applies to certain types of controls (see help for
FontItalic) if you try setting it for illegal controls you will get an
error. If you want to set only some controls to italics, you could: Check
the control's ControlType property (see help) before setting FontItalic;
Call On Error Resume Next before setting FontItalic to just ignore any
error; or use the Tag property to identify the controls you want to
manipulate, as in the example below...

------------------------------
Dim ctl As Control
Dim blnSetItalics As Boolean

blnSetItalics = (Me.DaysLeft > 0)

For Each ctl In Me.Section(acDetail).Controls
If ctl.Tag = "Italics" Then ctl.FontItalic = blnSetItalics
Next ctl
------------------------------


Max said:
I have a report that shows outstanding issues. There is a
field that calculates the due days of an item. I would
like to italicize the whole record/line if the days are
positive. (+) means the item is outstanding (-) means
there is still time left.

Any assistance on code would be greatly appreciated.
Click to expand...
Click to expand...
 
I have used a sneaky method to do this, particularly on continuous forms
where changing the property of a control affects all records on display.

Create 2 fields one has a record source such as
=IIf([AmountDue]>=1000,[AmountDue],"")

The second field has the record source =IIf([AmountDue]<1000,[AmountDue],"")

The first field can be formatted however you like, bold, red, italic

The second field can have its own format green, normal ... whatever

Once you have checked that they work as you require (the conditions can be
as complex as you like) then make sure that the 2 controls are exactly the
same size and put one exactly on top of the other.

As with any calculated control make sure that the control name is not the
same as any of the underlying field names.

Sounds clunky but works a treat.

Regards

Robin Ball
 
Seems like there's always *something* lurking in the shadows.

I had run that code snippet on a Win 2000 computer and it worked OK, but
this morning I went back and looked at the Controls.Count it is indeed 0.
While it worked, the 0 count does make one a bit uneasy.

Thanks for the info.


in message
While I like your last code example Lee, watch out when
using a section's Controls collection in A97 on a NT,
Win2000 or XP machine. There is a slight incompatability
between Access and those systems where a section's Controls
collection doesn't work. There's a KB article about
Me.Section(N).Controls.Count always being 0. It doesn't go
into other anomolous behavior, but there are other aspects
that fail as well.

A workaround is:

For Each ctl In Me.Controls
If ctl.Section = acDetail Then
If ctl.Tag = "Italics" Then
ctl.FontItalic = blnSetItalics




Lee said:
If you are using Access 2000 or later, you can use Conditional Formatting.
Assuming the DaysLeft field holds the number of days left, then for each
control that you want to set to italics define conditional formatting as:
Condition 1 = "Expression Is" ... "[DaysLeft] > 0". Then click the Italics
button for the Condition 1 format.

For Access 97 and earlier, and assuming you want to italicize all the fields
in your detail section, then add the following code to your detail section's
Format event:

------------------------------
Dim ctl As Control
Dim blnSetItalics As Boolean

blnSetItalics = (Me.DaysLeft > 0)

For Each ctl In Me.Section(acDetail).Controls
ctl.FontItalic = blnSetItalics
Next ctl
------------------------------

The FontItalic only applies to certain types of controls (see help for
FontItalic) if you try setting it for illegal controls you will get an
error. If you want to set only some controls to italics, you could: Check
the control's ControlType property (see help) before setting FontItalic;
Call On Error Resume Next before setting FontItalic to just ignore any
error; or use the Tag property to identify the controls you want to
manipulate, as in the example below...

------------------------------
Dim ctl As Control
Dim blnSetItalics As Boolean

blnSetItalics = (Me.DaysLeft > 0)

For Each ctl In Me.Section(acDetail).Controls
If ctl.Tag = "Italics" Then ctl.FontItalic = blnSetItalics
Next ctl
------------------------------


Max said:
I have a report that shows outstanding issues. There is a
field that calculates the due days of an item. I would
like to italicize the whole record/line if the days are
positive. (+) means the item is outstanding (-) means
there is still time left.

Any assistance on code would be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top