A field too far ?

[FatherJack]

I am trying to create a report that compiles data from 4 tables. there are approx 290 records returned in the recordset with 48 fields involved (many yes/no, others text). The query runs quite happily but the report returns 'run time error 7799' - "this form or report is based on a query that exceeds the limit for data in a single record"
does any one know of a way around this ? I have tried changing the table properties from text to memo as the help suggests but it makes no difference.
Please help - I have to produce the report. [sig][/sig]

 

[padinka]

Have you considered breaking it up into a report/subreport? Might work. [sig]<p>Trisha<br><a href=mailtoadinka@yahoo.com>padinka@yahoo.com</a><br>[/sig]

 

[padinka]

Microsoft says to try to compact the database see

http://support.microsoft.com/support/kb/articles/Q128/2/21.asp?LN=EN-US&SD=gn&FR=0

[sig]<p>Trisha<br><a href=mailtoadinka@yahoo.com>padinka@yahoo.com</a><br>[/sig]

 

[KirkJewell]

I don't have an answer for you but it does sound interesting.

Could you tell me more about the volume of data in each record (on average). Although 48 fields is a lot (it sounds like it could be 'normalized' to produced a header record and a set of detail records (I think Padinka is suggesting this in an earlier reply).

Did the compact work? I have a feeling it wont be the answer.

A database I am currently working on has 40+ tables. One of the tables has 94,000+ records with 20+ fields in each (all filled with something) and some with 40+chars in. And I have never had that error, even when combining this table with fields from four other tables.

I have however had a similar problem when trying to create an SQL string for a query with 15+ fields in the filter section. The error was caused by the SQL string being longer than 256 characters (I have had to limit the filtering to 10 fields max).

Is it possible that the same is happening with the report? [sig][/sig]

 

[FatherJack]

Thanks for the suggestions so far.
The compact did not have any effect.
In my original question i did not mention that data from one table is being retrieved through an odbc link. I dont think the sql string length is an issue as the query runs ok and the report is based on that query. The data volume should not be that great - the linked table is supplying five lines of address, the local tables as i say are 60% yes/no, 40% text - nothing greater than 50 characters, mostly a lot less. I have no knowledge of report/subreport implications - does the online help give all the answers ? (I should hope !!) [sig][/sig]

 

[KirkJewell]

Feel free to email me your report. (I wont be able to run it because I wont have the tables and especially the odbc link info. But I would at least be able to see what you are trying to do. (kirkjewell@bizonline.co.uk)

Don't put yourself down about the MS help files - they are good as reference material but not good for learning.

I would recommend two books (I know the cost is heavy).
An idiots guide(this gives you the knowledge that MS assume you have already)
+
An intermediate to advanced guide.

I spent 3 years bang my head against MS help files and poor manuals. I learnt more in 3 months! from two books.
(Microsoft have a habit of making sure their applications can do anything you might ever want (power) but seem to spend very little time supplying standard functions for the things you will obviously want to do (TopSpeed's Clarion - does the opposite)

Regards
Kirk [sig][/sig]

 

[FatherJack]

kirk, thanks for the offer. email on it's way !!
[sig][/sig]