Access 2003 Report - Conditional Formatting of Font Size

Posted on 2007-10-17
Last Modified: 2013-11-28
Hello all.
MS Access 2003 SP2
This database fills blanks on a US Customs form.  The Port Director has asked that I increase the font size on the entry fields so that it is easier to read when the fax is received.  (I know, there are 100 ways to email it and get a "pretty" copy, but they require the fax.)
Increasing the font size causes some records to extend beyond the allotted space on the form, cutting off portions of the data.
For example:  "Dublin, Ireland" fits fine, but "Purfleet, United Kingdom" gets cut off.
1) I cannot wrap the text.
2) I cannot modify the alloted size of the field.

I would like for the control to use font size 12 for everything that will fit, and either 11 or 10 based on the length of the data.  (I have determined that 10 is the smallest necessary.)

What I need is some combination of conditional formatting and whatever flags the "Can Grow" attribute.

I need to get this in production immediately, so any ideas will be greatly appreciated.
Question by:bgothard
    LVL 19

    Expert Comment

    i'm confused, do you mean form or report?
    LVL 6

    Accepted Solution

    Hi bgothard, something like this should work:

    Assuming you have a field called txtName in the Detail section of the report *and* you know that 23 character is about the maximum you can fit, add this to the report's Detail_Format sub:

    If Len(Me.txtName.Text) > 23 Then
        Me.txtName.FontSize = 10
        Me.txtName.FontSize = 12
    End If

    You will need to play with it a little because unless you're using a fixed-width font each word will be a different actual size depending on the letters in it (iii is smaller than MMM on the printed report).

    Hope that helps,

    Author Comment

    Sorry.  It is an Access REPORT.  

    The end result is a US Customs and Border Protection FORM 7512. (Official name.)  In this context, please ignore references to "form".  
    LVL 14

    Expert Comment

    Have you tried using Arial Narrow size 12.  The letters are bigger, but not as wide.

    If not, in the OnFormat event of the section where the field is. you can test for the len() and change the font size of the text box accordingly.  The len() is not perfect as we are not using proportional font, but is an easy alternative.

    Author Comment

    Thanks Grendel777!  That worked like a charm.  I added an ElseIf to step down through 11 to 10.  It's not a perfect solution because I'm using Arial, but it solves the problem with a minimum of fuss. (And isn't that what it's all about anyway?)


    I appreciate the suggestion, ragoran, and I'll keep it in mind.  I already implemented Grendel777's solution before I read yours.  (The code, not the font.  The narrow font doesn't fax well, either.  I tried that earlier and they didn't like it.  Good suggestion, though.)

    Thanks again!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now