Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No of fields on a report (2002/2003)

    When using Access 2002 I created a large report with about 325 fields on it. I did have more than that and cut it down line by line until it worked. Never did find in the Help what the max number is allowed.
    Have since upgraded to Access 2003. In the same database (Still using Access 2000 format) I have created a similar report using Access 2003 and get the error message "Too many fields defined". It worked with 220 fields, but as soon as I added the totals it bombed. Help tells me 255 fields is the max.
    BUT
    The original report still works in the new environment!
    How can this be?
    How can I get my new report to work in the same way that the original report runs.

    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No of fields on a report (2002/2003)

    First, see if compacting the database fixes it (Tools | Database Utilities | Compact and Repair Database.)

    The record source of a report can contain a maximum of 255 fields; this is a general limitation that applies to tables and queries; it has been so in all versions of Access. A text box with a control source such as =Sum([FieldName]) does not count as a field.

    There is also a limit to the number of controls on a report; in versions up to and including Access 2002 this was 754 throughout the lifetime of the report. I don't know if that has changed in Access 2003.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No of fields on a report (2002/2003)

    Thanks for a fast response.

    Unfortunately, compacting did not work.

    Your comment "754 (controls) throughout the lifetime of the report" intrigued me. Could you elaborate on this some more?
    Based on the comment I imported the report to another database and then reimported it again to the original database. Unfortunately, this did not resolve the problem either.

    John

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: No of fields on a report (2002/2003)

    I've encountered same problem with very complex forms/reports in A2K and AXP. Compacting db or importing form/report into a new db will not resolve problem. Your only viable option is to rebuild report by creating new report and doing a lot of cutting & pasting, and being careful not to "waste" controls. As noted, the limit of 754 controls applies to the "lifetime" of the form or report. Meaning if you add control & later delete it, it still "counts". This MSKB article describes the limitation:

    ACC2000: Errors Converting or Importing with 754 or More Controls

    To quote article briefly:

    "Access 2000 limits the total number of controls, sections, and fields in the RecordSource property to 754. For example, if a form contains a detail section, 500 unbound text boxes, a Visual Basic for Applications module, and a record source that contains 255 fields, you receive the errors listed in the "Symptoms" section when you convert or open the database. This occurs even though none of the fields in the RecordSource property are actually used on the form."

    Note that any fields in RecordSource count too, whether or not used in the report. This article addresses Access 2000 but it appears that the limitation has not been changed in Access 2003 (I'm still using A2K/AXP so cannot confirm or deny this).

    HTH

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No of fields on a report (2002/2003)

    Thank you for the "lifetime" elaboration Mark.
    I have a total of 451 controls on the report itself and 160 in the underlying query. <754!
    Cutting and pasting a whole section of the form at one time to a new form did not resolve the problem. Am I likely to have picked up the "history" by doing that? Do I have to cut and paste each individual control???????? Sorry to ask such a question but that is one hell of a lot of work again, as you will well appreciate and I wont know if it works till I get to the end!

    John

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No of fields on a report (2002/2003)

    The underlying query has nothing to do with the number of controls on the report. Controls include labels, lines, graphics, boxes, subreport and any other objects (bound or unbound) you might have dropped on the report, including pagebreaks. Are you saying that cutting and pasting a section of the controls into a new report triggered the error on the *new report*?? Removing controls won't affect the current report, which will still have reached its lifetime limit, but you shouldn't see the error on the new report.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: No of fields on a report (2002/2003)

    I would not advocate copying & pasting one control at a time - when rebuilding form/report the easiest way would be to select entire section (or tab page if using tab control on form) then copy/paste in a new report. If you tried this once and did not work, do not recommend repeating process. Not sure if your problem is result of too many controls on report, or too many fields, which is a different (though related) issue. For example, see MSKB 291556:

    ACC2002: "Too Many Fields Defined" Error Message When You Save a Table

    As noted in article:

    "The internal column count that Microsoft Access uses to track the number of fields in the table has reached 255, even though you may have fewer than 255 fields in the table. This can happen because Access does not change the internal column count when you delete a field. Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify."

    Compacting the db is supposed to fix this problem. Other option is to recreate the table (and any relationships). Note that you get same error if you try to run query with more than 255 fields, or run report whose RecordSource is an SQL statement equivalent of query that selects more than 255 fields from one or more tables. As test, trying to open DAO or ADO recordset based on inner join between 2 tables, each with 255 fields, likewise resulted in "Too many fields defined" error.

    Your best option may to be somehow simplify the report, if possible, and to follow recommendations in MSKB article for "Too many fields" issue in case this is cause of problem.

    HTH

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No of fields on a report (2002/2003)

    Thank you Charlotte and Mark.
    The underlying query (a separate query) works fine. It is only when I run the report that I get the error message "Too many fields defined". Going into the Context Sensitive Help points to Error 3190 which is the "table" problem of non released field count.
    If I reduce the controls on the report (old, or recreated section by section), down to about 255 (they are a bit hard to count accurately) then the report runs fine. Put in one more row of 13 controls and get the error.
    A report that this new report was modelled on (different queries, tables etc) with about 312 controls runs fine?

    John

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No of fields on a report (2002/2003)

    Although I don't understand why you would be limited to about 250 controls on a newly created report, I wonder why you need so many. Perhaps you should reconsider the design of your report. If your report contains many repeating elements, you might let the record source handle the repeating, instead of the report. It might also be possible to split the report into a main report and one or more subreports. The limit holds for each subreport separately, so a report with a subreport can hold 2x754 = 1508 elements. I don't know how your report is set up, so I can't give specific advice.

  10. #10
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No of fields on a report (2002/2003)

    Thank you All.
    I am very happy to report the problem is solved.
    POINT TO NOTE
    I copied every individual cell (by holding down Shift and clicking) to copy them to the blank report. Then it worked. Doing a group selection DID NOT WORK.
    Thanks again.

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •