[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Exceeds limit for data in a single record

Posted on 2011-02-22
11
Medium Priority
?
1,703 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Expert Comment

by:HainKurt
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 61

Expert Comment

by:HainKurt
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 61

Expert Comment

by:HainKurt
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 61

Expert Comment

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

Accepted Solution

by:
HainKurt earned 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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