Text field cutting off chars at 255

Question

Text field cutting off chars at 255

chrismol

SSC-Addicted

Points: 473
More actions
December 6, 2009 at 8:44 am

#213278

I have a reporting setup where my users connect to SQL Server 2000 using Access 2003 and 2007. When they run a query in Access and pull back a text field, the text in that field gets cut off at 255 chars. This is a problem because this field contains survey comments and is frequently more then 255 chars.
Is there a way to fix an Access query to return more than 255 chars?
I did find that if I use an Access Report to pull the text field from a linked table rather than an Access query, it will return all the text fields that are > 255 chars. However, the Access Report does not allow query parameters so it's basically useless.
Any help with this would be appreciated.
C

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rf44 SSCommitted Points: 1515 More actions · Answer 1

Verify if the field that is trucated in Access is not defined as Varchar(Max) or NVarchar(Max) in the database. In such cases, Access incorrectly converts the column defined with (Max) length to a Text(255) data type instead of a memo.

The solution consists in changing (Max) to a number, e.g. (4000) or (8000), in the table definition.

chrismol SSC-Addicted Points: 473 More actions · Answer 2

rf44,

The field is truncated in Access and is a text field with no char limit. However, the field lives in a linked table from SQL Server and is fine in here (not truncated). The issue I'm having is that I download data every night from MySQL prod server to my internal reporting SQL Server. I do this with DTS. The DTS jobs are set to drop/create tables every night to keep the the data clean. The DTS job by default picks up the data type from MySQL and uses it to create and populate the table in SQL Server. The data is passed to SQL server fine. It's when I run a query in Access that I get the truncated field.

Any more ideas?

Thanks,

C

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 3

Access Reporting fields -

One option is to use a function in the Access report rather than a fixed field.

A call to a function can include code, parameters, TSQL, a Pass-Through query, or other options that return the value to your field.

The same thing is true for Excel Object Model reporting.

Bringing in a recordset via programming code will indicate where the record limitations are at.

chrismol SSC-Addicted Points: 473 More actions · Answer 4

Rob,

Thanks, but I don't know how to use Access Reports to write a query. Can Access reports run like a query? I need to run Access queries with parameters and output them to Excel for users to sort, etc.

Any more info you could provide would be helpful.

Thanks,

C

Uripedes Pants Hall of Fame Points: 3701 More actions · Answer 5

the problem is that Access data type "text" IS limited to 255 chars. Use the "memo" data type, it holds 65k

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 6

http://www.excelforum.com/access-programming/696798-recordset-query-how-to-open-and-retrieve-db.html

Good code (connected to SQL DB) examples for both Access / Excel using programming is avalilable at this site. The right forum for the right solutions...

Yes, Access (and Excel) has limitations as your being advised.

Code may help you set variables to evaluate sizes and truncate or parse them to fit into your specific application needs.

On this forum: I posted code for a SQL Pass-Through Query.

http://www.sqlservercentral.com/Forums/Topic548731-149-2.aspx?Update=1

Let SQL perform the crunch locally. Then bring the results back into variables. From there, convert the data types to match your reporting interface.

RJ Smith-262986 SSC Enthusiast Points: 110 More actions · Answer 7

Here is something to consider...

Within the Access query...

*If you apply formatting

*If the query uses Group By

*If it is a SELECT DISTINCT query

...the query will truncate text fields to 255 chars.

wolf

chrismol SSC-Addicted Points: 473 More actions · Answer 8

I resolved this today. I did have a DISTINCT call in my query which I removed and then was able to see more then 255 chars in Access (thanks RJ). However, when I then tried to "analyze with Excel", the chars were dropping off again at 255. So from Access I did an Export to Excel from the query results and it worked, all fields with more than 255 chars displayed ok.

Thanks to all for their help.

C