Query Exceeds limit for data in a single record

I am getting an error message that states: "This form or report is based on a query that exceeds the limit for data in a single record." It prevents me from printing this report on a particular record. I can print it occassionaly, but I usually get this message. Does any one know what might be causing it and what would a potential solution be?
dlord54Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
Limits:

http://www.databasezone.com/techdocs/acclimit.html

Number of characters in a record (excluding Memo and OLE Object fields)  4,000; 2,000 for Access 2000 and 2002  
0
 
Rey Obrero (Capricorn1)Commented:
posting
the SQL statement of the query
name of table and field Data types

will help.

0
 
xavixsbCommented:
Access 2007 has a limit of 255 number of fields in a recordset returned by a query

See:
http://blogs.office.com/b/microsoft-access/archive/2006/06/05/access-2007-limits.aspx

If other version of access the limit will be the same
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
dlord54Author Commented:
Attached is a file that has the SQL Statement and the table and field data types.
SQLandTableDefs.doc
0
 
HainKurtSr. System AnalystCommented:
your issue is not 256 columns!
your issue is total number of chars for all columns exceeds max allowed size (I guess 2000 char per row)

either remove some columns from rows, or maybe you can convert some columns into memo...
0
 
HainKurtSr. System AnalystCommented:
or you can divide that query into 2 queries and join with id on report... not sure how to do that...

I mean

select id, col1,col2,...,col140 from table

-->

select id, col1,col2,...,col169 from table

and this query,

select id, col71,col72,...,col140 from table

and on report somehow join id's and use columns from two query instead of 1


0
 
dlord54Author Commented:
I tried converting some to memo, but that didn't seem to help. But I will see how many char per row I have.
0
 
HainKurtSr. System AnalystCommented:
i guess you can use master/detail report and join with id...
0
 
dlord54Author Commented:
I will try that and see what happens.
0
 
HainKurtSr. System AnalystCommented:
you can use Trim(...) function for all your text columns, it may help if they have trailing spaces...
0
 
dlord54Author Commented:
Thanks for your help. The record definitely exceeded the limit.
0
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.