• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 954
  • Last Modified:

This form or report is based on a query that exceeds the limit for data in a single record

Hi all.

I have an Access 2003 form that is displaying the error: "This form or report is based on a query that exceeds the limit for data in a single record"

When I go to the actual query and enter the itemnumber it gives me the error, if I enter another item the query runs.

The query pumps out 172 fields, I've been reading around and I think it's because the query can't pump out more that 2000 characters per row. Is this true? If so, how can I get this to work?

Thank you in advance!
0
printmedia
Asked:
printmedia
  • 3
1 Solution
 
Helen FeddemaCommented:
I would start by taking a look at the problem record.  It may have something odd in one field.
0
 
Helen FeddemaCommented:
By record in this case I mean the query filtered by the item number that triggers the error.  It may be based on several tables, so you need to determine which table has the field that might be causing the problem.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The query pumps out 172 fields, I've been reading around and I think it's because the query can't pump out more that 2000 characters per row. Is this true? If so, how can I get this to work?>>

  Had never heard of that error before in a query, but the page size is 4096 and since it's uni-code, then means approx 2048 characters of storage in the records (which excludes OLE and Memo) fields.

<<Is this true? If so, how can I get this to work?>>

  So next question is: 172 fields?  Sounds like you need to re-work the design of the reports or tables.  

  One possible work around for the report is the use of a series of sub-reports each with part of the data, but you'll need to fill in some more detail on exactly what it is your putting on the report before we can say if that's a good approach or not.

  Another is using un-bound controls and fetching the data in pieces and placing it into the control for each record.

Jim.

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Error 7799:

This form or report is based on a query that exceeds the limit for data in a single record.@Exclude any unnecessary fields from the query, or change some of the field types to Memo in the original tables.@@1@@1

Never have seen this error.

mx
0
 
Helen FeddemaCommented:
I have also never seen this error, and I have been working with Access since the beta of v. 1.0!
0

Featured Post

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.

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