Solved

Query Exceeds limit for data in a single record

Posted on 2011-02-22
11
1,574 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:dlord54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34953993
posting
the SQL statement of the query
name of table and field Data types

will help.

0
 
LVL 3

Expert Comment

by:xavixsb
ID: 34954075
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
 

Author Comment

by:dlord54
ID: 34954121
Attached is a file that has the SQL Statement and the table and field data types.
SQLandTableDefs.doc
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34954234
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34954260
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
 

Author Comment

by:dlord54
ID: 34954265
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34954275
i guess you can use master/detail report and join with id...
0
 

Author Comment

by:dlord54
ID: 34954284
I will try that and see what happens.
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 34954295
you can use Trim(...) function for all your text columns, it may help if they have trailing spaces...
0
 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 34954337
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
 

Author Closing Comment

by:dlord54
ID: 34955443
Thanks for your help. The record definitely exceeded the limit.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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