Crosstab Query Error: The setting you entered isn't Valid for this property (1 Viewer)

[Bilbo_Baggins_Esq]

I have two tables, both with identical structure but different contents.

153 Total fields each
710 records in one and 791 records in the other.

Field 0 = RecordID = AutoNumber
Field 1 = File_Date = Date/Time
Fields 2 through 152 are all memo fields
Field 153 is "File_Name" = text

I created separate crosstab queries for each table.
File Name is Group By
RecordID is count

When I ran EACH the queries I got the error:
"The setting you entered isn't Valid for this property"

However, when I click the OK button, the queries BOTH RUN and show expected results.

I started troubleshooting the errors
All of the research I found was relating to VBA properties and nothing on plain queries.
However, one of the reports I read said something about turning caching off and that fixed the error.

So, one of the tables I opened and turned the Unicode compression off (on the File_Name field)and the error in the query went away after I saved the table.

However, after making the same property change to same field in the OTHER TABLE, it still throws the error.

Now, interestingly, the first table, the one that NO LONGER throws the error, actually uses much fewer (85) of the 150 available memo fields.
When I made the Unicode Compression change, It threw an message about "Data Integrity change" (or something like that) and I clicked OK and the table saved and the error in the query went away.

HOWEVER, the other table uses MANY MORE (148) of the available 150 Memo fields.
When I made the same change in that table to "Unicode Compression" it threw the same message about "data integrity change" BUT when I clicked OK, it threw an error "record too large".
So, I closed the table without saving the changes and when I went back in to try it again, I noticed that it actually did save the change to the property.
But the query off that table still throws the same error.

One more odd thing about the first table (the one whose query now runs fine).
If I change that property (Unicode Compression) back to YES, the table saves fine ANF the query now throws no errors REGARDLESS of the setting for Unicode compression.

VERY ODD!

Please help?

 

[MarkK]

I have two tables, both with identical structure but different contents.

There is no reason to do that. If the data in the two tables differs by a single dimension, then add a field and store that dimension's value in the single table. But having the same structured data in more than one table is a make-work project, a design flaw, a headache, etc...
Hope this helps,

 

[Bilbo_Baggins_Esq]

Seriously?

The two tables have ENTIRELY DIFFERENT DATA and an unknown number of fields to each record.
While the max num of fields on one table is usually 85, it is not always so, sometime less and could at somepoint be more.

AND THE FIELD HEADINGS ARE ALWAYS DIFFERENT for the two datasets and the data is ENTIRELY DIFFERENT CONTENTS!

Why would you add that completely irrelevant comment to my thread?
Please don't answer, it will just make it look liked there are enough comments now the thread will look answered.
ARGH!