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

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?
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:
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: http://allenbrowne.com/ser-63.html
...This may be applicable if you are aggregating this field.

What happens if you create a new blank report in 2007 and copy over the controls, recordsource, and code from your 2003 version?
swpa_wntAuthor Commented:
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.)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

swpa_wntAuthor Commented:
Also, what do you mean by "aggregating" the field?  Is that something that happens in the way the query "calculates" the value?
Jeffrey CoachmanMIS LiasonCommented:
<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.
swpa_wntAuthor Commented:
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!
Jeffrey CoachmanMIS LiasonCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.