Too Many Fields Defined Problem 1

[C4rtm4N]

Hi,

I have quite a complicated report based upon a pass through qury in my Access 2000 front end to our SQl database. I'm getting a 'too many fields defined' error after tweaking a couple of my calculations but am mystified as to why.

Browsing through the report there are only 84 fields and 309 text boxes / other elements & I thought that the hard limit was 255 fields plus a total of around 760 elements. Am I wrong ? Any ideas how I can get around this?

The elements of the report are quite long formulae with a couple of checks for zeros & nulls at the start of them - does Access count each use of a field separately ?

Thanks

Steve

 

[dhookom]

You might want to try compact your database to see if that frees up some control space.

Too many fields does suggest that your report's record source has too many fields. A properly normalized table rarely requires more than about 25 fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[C4rtm4N]

Thanks Duane

I've already compacted & there's no change.

What do you mean by the report's record source? If it's the query, it's only got 84 fields (which are needed). Does the fact that it's a stored procedure based on my SQL server make a difference ?

Steve

 

[dhookom]

While your report is open in design view, open the debug window (press Ctrl+G) and enter
? Reports("name of your report").Count

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[C4rtm4N]

That comes back as 301 but I don't understand why.
Is that the number of fields ?
Are some hidden & what can I do to reduce them ?

Steve

 

[dhookom]

That number should be the number of controls on your report. You might have only 84 fields selected in your report's record source but how many total fields are there in all tables involved?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[C4rtm4N]

There will be more than 84 fields in all tables but I thought that Access would,'t see these as the stored procedure that I'm using creates another table then groups things together based upon that table.

Will I be better off making a table with the grouping that I need to do so everything is based upon the one thing ?

Thanks

Steve

 

[dhookom]

I can't answer. I have no experience dealing with this combination of lots of fields, stored procedures, and your error message. Some questions would set me off to re-create tables, records, and other environmental attributes to test. In this situation, I can't afford the time. I also haven't heard of similar situations.

One last wag, if you are creating totals queries, what is the greatest number of fields you are attempting to group by?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]