Too Many field defined but well under 255 fields 1

[laina222]

Access keeps giving me the error message "too many fields defined" when I try to run a query that has only 90 fields defined. I read somewhere that compacting the database can help solve this problem because the column counter may be off, but that hasn't seemed to solve my problem. Has anyone else encountered something like this and been able to solve?

Thanks for any help in advance.

 

[PHV]

You may try to copy the SQL code and paste it in the SQL pane of a brand new query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[laina222]

Actually, that's exactly what I tried, so maybe it's not the counter?

 

[MichaelRed]

The definition of "Field" in the statement needs to be CAREFULLY reviewed / understood. Ms. A. (at least) counts each use of a field in the SQL Statement seperatly (wheather it is in as an index, criteria, part od a Join) or otherwise).

Thus, if each (actual/output) field is (even by happenstance) indexed you would automatically get to double the field count (up to 180). A single criteria on each would then easily send the total to 270, well beyond the limit.

I'm not suggesting that you have (deliberatly) done snything quite so rash, but am just trying to illustrate some common pitfalls.

I have seen at least one example of the first (adding indicies) issue innocently engendered by changing the criteria for "automatically" indicies to a query.

Please review the ACTUAL SQL statement, and count each instance of a field name throughout the entire statement. If this is WELL below the limit (255), post the statement. If it is even near the limit, please review the need for each and revise the query. If steps one &/or tow fail to resolve the issue, I could only suggest that you generate seperate queries with a common Key fiels and a an additional one to re-join the partial results into the single desired recordset.





MichaelRed