asked on

Query Exceeds limit for data in a single record

I am getting an error message that states: "This form or report is based on a query that exceeds the limit for data in a single record." It prevents me from printing this report on a particular record. I can print it occassionaly, but I usually get this message. Does any one know what might be causing it and what would a potential solution be?

Rey Obrero (Capricorn1)

posting
the SQL statement of the query
name of table and field Data types

will help.

xavixsb

Access 2007 has a limit of 255 number of fields in a recordset returned by a query

See:
http://blogs.office.com/b/microsoft-access/archive/2006/06/05/access-2007-limits.aspx

If other version of access the limit will be the same

dlord54

ASKER

Attached is a file that has the SQL Statement and the table and field data types.
SQLandTableDefs.doc

HainKurt

your issue is not 256 columns!
your issue is total number of chars for all columns exceeds max allowed size (I guess 2000 char per row)

either remove some columns from rows, or maybe you can convert some columns into memo...

HainKurt

or you can divide that query into 2 queries and join with id on report... not sure how to do that...

I mean

select id, col1,col2,...,col140 from table

-->

select id, col1,col2,...,col169 from table

and this query,

select id, col71,col72,...,col140 from table

and on report somehow join id's and use columns from two query instead of 1

dlord54

ASKER

I tried converting some to memo, but that didn't seem to help. But I will see how many char per row I have.

HainKurt

i guess you can use master/detail report and join with id...

dlord54

ASKER

I will try that and see what happens.

HainKurt

you can use Trim(...) function for all your text columns, it may help if they have trailing spaces...

ASKER CERTIFIED SOLUTION

HainKurt