Text Field on Report used to show more than 255 characters. Now it doesn't.

Posted on 2012-08-15
Last Modified: 2012-08-16
I've got a report in Access with an underlying query that includes the following:

IIf(IsNull(A.Description),B.ProjectName+" "+B.Description,A.Description+" "+(IIf(IsNull(A.Notes),"",A.Notes))) AS BigDescription

The result, BigDescription, is then displayed in a Text Field on the report.

Here's the problem. Three years ago, the Text Field displayed the entire contents of BigDescription; but today, the Text Field cuts off after 255 characters.

Please note that the report was created in Access 2003 (or earlier), and we have upgraded to Access 2007 in the last couple of years. (Yes, we're way behind.)  HOWEVER... if, using Access 2007, I open a backup of the database from three years ago, the Text Field on the report still displays MORE than 255 characters; but if I open our current working copy of the database, the Text Field is cutting off at 255.

If this problem was just related to 2007, then I would've expected the old backup version to also show truncation in the Text Field when opened with 2007, but it doesn't.  So I'm mystified.

There have been no changes to the relevant fields in tables A or B, and no changes to the query syntax. Nor can I detect any difference in the Text Field properties between the backup copy of the database and today's working copy.

Any ideas?
Question by:swpa_wnt
    LVL 74

    Accepted Solution

    1. This is why I put my "calculated fields" in a query, then use this query as the source for the report.
    This way you can open up the query and verify the contents.

    2. Your Syntax is not really proper for MS Access:
    Try something like this:
    IIf(IsNull(A.Description),B.ProjectName & " " & B.Description,A.Description & " " & (IIf(IsNull(A.Notes),"",A.Notes))) AS BigDescription

    3. Always do the following before using an Access db in a new version:
    a. Run the Compact/Repair Utility
    b. Compile the code

    4. Make sure you have all your Service Packs and updates installed for Office *and* Windows

    5. See here:
    ...This may be applicable if you are aggregating this field.

    LVL 29

    Assisted Solution

    What happens if you create a new blank report in 2007 and copy over the controls, recordsource, and code from your 2003 version?

    Author Comment

    I'm sorry -- I seem to have given the impression that I'm using 2007 to open up a 2003 mdb file, and then having problems when I do that.

    On the contrary, the database has already been upgraded to 2007.  When we did that, there were no problems that I recall, but for the sake of argument let's say that this problem is related to the upgrade to 2007 and I just never noticed it before.  Even so, here's what I've just now noticed:

    1) If I use 2007 to open our current working copy of the database (which is the 2007 copy), the Text Field is truncated.

    2) If I use 2007 to open up an older backup copy of the database (which may be 2003), the Text Field is NOT truncated.

    So, I will attempt to re-create the report as IrogSinta suggests, as that may be the most straightforward way to rule out form or field corruption.

    And as boag2000 suggests, I will also run the Compact/Repair utility again and compile the code.  QUESTION: How do I compile code in an Access db?

    (P.S. The BigDescription "value" is already being calculated in a query, which is the source for the report.)

    Author Comment

    Also, what do you mean by "aggregating" the field?  Is that something that happens in the way the query "calculates" the value?
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <Also, what do you mean by "aggregating" the field?  Is that something that happens in the way the query "calculates" the value? >
    The info in the link explains this, but in reading your subsequent posts, I don't think it is applicable.

    Author Comment

    An update... I added a field to the query to measure the length of BigDescription.  In the query results, this FieldLength field could show a high value like 438, but the BigDescription field itself was still truncating to the first 255 characters!  SO, the problem wasn't with the Text Field on the report at all. It was in the query.

    I looked at the allenbrowne link that Jeff provided above, and saw that one possible area to check is "uniqueness." And it turns out that our query HAD been modified at some point to add DISTINCT to it. Thus, Access had to compare the text field to all the other records, and it was the comparison that caused truncation. When I removed DISTINCT from my query, the BigDescription is no longer being truncated.

    Full disclosure -- Now I remember that *I* added the DISTINCT modifier to the query a while ago because I was getting duplicate results, and it was a quick way to "fix" it. So I guess I need to go back to trying to find a solution for *that* problem.  But that will be another thread.

    Thanks for the help, Experts!
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    18 Experts available now in Live!

    Get 1:1 Help Now