Text field truncated in report 1

[DrStephen]

I have a report that runs off of a query. I'd like the text field to expand to whatever size is required. The problem is that the text field in my report (Report 2) is truncated. I haven't counted, but I willing to bet it's chopped at ~255 characters.

I have another report that accesses this same field. The text in that report (Report 1)does not get truncated. I did have the problem in Report 1 when I first wrote it, but somehow it got resolved via this forum. The real problem is that I can't remember the fix. The field in question is a memo field in the data table.

...any ideas?

 

[dhookom]

Check the report's record source to see if you see all the characters in datasheet view. If you don't, I expect you have a phrase in your SQL like "DISTINCT", "GROUP BY", "DISTINCTROW", "UNION"...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lag1]

I am having the same problem with any of my queries (and even importing from Excel, which I had to do using VBA). Access evaluates all queries of "memo" fields as "text", which is really stupid since Access knows that it is "memo". I found a solution that is not the simple one I wanted, but will do the trick -- you must create a table for your query results that defines the long fields as "memo" then do an append query, thus filling the table with the results that you want to use.

http://support.microsoft.com/kb/207668/en-us

I found your posting in searching for a better way, and please let me know if you do, but in the meantime, this does work.

 

[lag1]

Sorry for the multiple postings -- I keep having second thoughts, like Columbo. My hope is that someone knows how to edit this simple query to NOT truncate the results without having to write it to a table as described in my previous post.

Here is the simple query I am running that truncated the field "Names" to 255 characters. It gives me a good count of the length of the field, but the query results are only 255 characters long:

SELECT SCData.Key, SCData.Names, Len(SCData!Names) AS Expr1
FROM SCData
WHERE (((Len([SCData]![Names]))>255) AND ((SCData.Series)="S108093"))
ORDER BY SCData.Key;

Thanks!

 

[dhookom]

Do you have any value in the format property of the Names column? For instance, I just created a table in Northwind with two memo fields (Names and Names2). I pasted the same very long text in each field in the same record. I set the format property of Names2 to [red]>[/red].

When viewing either the table or a query based on the table, I only see 255 characters in Names2 while Names (which contains the exact same values) displays many more characters.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]