Avatar of Frank Sellers
Frank Sellers
Flag 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.

Thank!
Final-Means-Report---Online-Vers.bmp
Microsoft Access

Avatar of undefined
Last Comment
trbaze

8/22/2022 - Mon
Jim Dettman (EE MVE)

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

Jim.
ASKER CERTIFIED SOLUTION
trbaze

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

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"

;-)

JeffCoachman
Jim Dettman (EE MVE)

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:

 [Red]

So you might have a format specification like this:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

Jim.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Frank Sellers

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

Thanks!
trbaze

Your welcome.  Glad we could help.