Access Reports

[Guest_imported]

I am getting the message "unable to create report because I chose too many fields, try again with fewer fields" when I try to run this one report. I looked in the Access specifications and it states that the maximum number of controls and sections in a form or report can be 754. I have less than one hundred. The report is generated from a query that is pulling data from 19 tables with up to 4 to 8 fields from each table being shown on the report. The generated report will fit on only one page. This is basically a daily summary of the previos day's activity at my company. Any help with this will be greatly appreciated.

Thanks in advance.

 

[LittleSmudge]

I think the problem lies - not in the report itself - but in the query that you are using to gather the data.

What format of query are you using ?


G LS

 

[mmsbasd]

I agree that your problem is most likely due to the query and not the report itself. Here is a Microsoft article that provides tips on how to optimize your queries and possibly resolve your issue. Hope this helps.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q103429

NOTE: Cut and paste the complete URL into your browser. The posting cuts off the address because of the ";" in it.

 

[Guest_imported]

I am using a select query. MMSBASD, the link you gave me isn't going anywhere. I get a link not foound message.

 

[oharab]

repair and compact your database.
If you add a control onto a report and delete it, then it still counts as a control used.
Compacting the database should reset the counter.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[Guest_imported]

I tried repairing and compacting. It didn't help. What I didn't tell you is that the query does run by itself and I did buid a form with that query and it does open and populate itself. Thanks

 

[oharab]

Final try!: create a blank mdb and import everything from the old into the new.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[mmsbasd]

If you go to Microsoft's Knowledge Base, scroll to the bottom of the page and you can search for article number 'Q103429'. This article describes, among other things, that there is a memory limit to how voluminous a query/report can be. This would explain why the FORM works but not the report. Try this:

Create a simple report that lists maybe half of your fields. If it works, add a few more to it until you get the error. If you don't get the error, after adding all the fields in, then your original report probably has an expression in it that is pushing you past that memory limit. If you can alter your working report to work for you, then great.

If you get the error at any point, one option would be to break up the report into two separate reports and create a macro that would run them concurrently.

 

[Guest_imported]

MMSBASD,

I tried what you suggested by removing some fields from the query and it then worked, however, as I put them back into the query and ran the report, I got the same error again. I am splitting the query into two separate queries and then I am going to try to add one as a subreport in the main report. I'v never done this before. I have read that this can be done in my Access 97 Developer's handbook. I have failed to mention that I an working with Access XP in a 2000 file format. The other thing that is strange is that I searched for subreports in the HELP section and nothing came up.

Thanks again for everyones help

 

[xollef]

Hey.

Had the same problem as you had when trying to create a report by using the wizard or just plain new report, and having a query as the source from which it should get the data.

However for me it worked by going to the "query area" and rightclicking the query that the report should be based on and then choose save as -> choose report -> save. That way it didn't give me any errors and I got my report : )

 

[jebus]

I am also getting this message "unable to create report because I chose too many fields, try again with fewer fields". My report can be created directly from a table I enter the data on. The report I am trying to create has a grouping field by 'person'. Each person has 50 'goals' to be evaluated on. I can create the report with up to 10 gaols, but this is all that the report can physically fit on the report page. I've tried a couple subreport options to include the other goal fields but these aren't working. Any advice?