Link to home
Start Free TrialLog in
Avatar of Frank Sellers
Frank SellersFlag for United States of America

asked on

MS Access Report - Text Field Formatting "Magic" without VBA

I know this is going to sound very strange, but attached is a screen shot of the last page of a medical school report in an MS Access 2003 database, sorted by score from best to worst.

As you can see, in the Score column the original programmer un-italicizes text and changes the font color from blue to red.  Likewise, if a student's percentage (% fields) was less than 70, the programmer changed the font color from blue to red and from regular text to bold.

Whenever I've tried this in the past it's changed to formatting for every record in the report, so I was very impressed when I saw this.  I couldn't wait to dig in and find out how the programmer did it.  But to my astonishment I can't find any VBA either behind the report or in a module that would explain how it's done.  Likewise, in the text fields themselves there are no conditional formulas.  No macros, no hidden objects - nothing!

If anyone can possibly explain how's the programmer pulled off this magic stunt, please explain it to me.

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Look at the format property of the control....

Avatar of trbaze

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am not aware of any other technique to do this except form VBA or Conditional formats

Are you quite *sure* that neither technique was used?

If not, then Jim's post may provide the answer.

If not, then you may have to post a generic sample of this DB that displays this "magic"


You can specify a color as part of the format statement, but your limited in the number of conditions that you can specify:

1. One section only The format expression applies to all values.
2. Two sections The first section applies to positive values and zeros, the second to negative values.
3. Three sections The first section applies to positive values, the second to negative values, and the third to zeros.
4. Four sections The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null values.

 Along with that format specification, you can provide a color, but are limited to:

Black, Blue, Green, Cyan, Red, Magenta, Yellow, White

The color must appear in brackets:


So you might have a format specification like this:


Avatar of Frank Sellers


I've used Conditional Formatting in Excel plenty of times, but I didn't even know it existed in Access; I've always just gone straight to VBA.  The original developer pointed it out to me but I'm awarding this to trbaze for being the first to point it out, and he's the only one I'm awarding it to because he also provided a helpful link.

Avatar of trbaze

Your welcome.  Glad we could help.