[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1752
  • Last Modified:

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?
0
dlord54
Asked:
dlord54
1 Solution
 
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
 
dlord54Author Commented:
Attached is a file that has the SQL Statement and the table and field data types.
SQLandTableDefs.doc
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
HainKurtSr. 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
 
dlord54Author Commented:
Thanks for your help. The record definitely exceeded the limit.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now