Access 2010 program bug in crosstab queries??? OK in Access 2007. No fix in 2010???

[BillG]

I have several simple and complex crosstab queries that are proven in Access 2007 and work fine. No errors.

Now using Access 2010 for several months now, I have a major problem with the same crosstab queries.

In my crosstab queries one of the parameters is the "column heading" on the crosstab line and "group by" on the total line. The query runs perfect just like in Access 2007 as it is suppose to.

However, in Access 2010 with the query saved if I open the query I get the following error message:



"You must enter Group by in the Total row for a field that has column heading in the crosstab row" and with the query design loaded in the background. I click on the OK button and it automatically closes the query. It won't let me into the design screen in order to correct it.

The problem is twofold:

1. The query is set correctly "group by" in the total line for the crosstab column heading and is saved and runs perfect. But Access 2010 changes the "group by" to "expression" either when saved or when opened, which is causung the error message above because Access 2010 changed what I had "group by" to "expression" after it was saved correctly.

2. Because when I get the above error and click on the OK button and it automatically closes the query which doesn't allow me to get into the query design to correct the field. Therefore the only way I can get it corrected is to completely reconstruct the crosstab query from scratch. I don't know/remember all the columns, paeameters and settings in the original query to reconstruct it.

Even so, when I do reconstruct the query and it is working fine at some point the cycle repeats and I open the query the next time and Access 2010 has again changed the parameter from "group by" to "expression" and now I am locked out again.

These queries are over a year old and worked fine 100% of the time without any errors in Access 2007.

To make matters worse it doesn't do this on all crosstab queries. Some it never changes the parameters and the query is fine while other crosstab queries it changes the parameter. Even a query it changes I can open the query several times or the first time before it changes the parameter. there is no rhyme or reason why it does it.

Access 2010 is fully patched and current on all Microsoft updates. So if there is a bug they don't semm to be aware of it.

This is causing major problems to the point I need to roll back to Access 2007 so I don't lose these queries and have to reconstruct only for it to happen again and again.

This is such a major problem that other users would be having the same problem and notify Microsoft to fix the bug. But that doesn't seem to be happening, so it all doesn't make any sense.

HELP!!!!!

[June7]

Could edit the query in SQL View window. However, no idea if that will eliminate the issue.

I don't use crosstabs much so don't know if this is bug in 2010.

Depending on your data, there are alternative methods to using crosstab. Review https://www.accessforums.net/reports...ast-30413.html

[BillG]

SQL view is not an option in right clicking the query. Only open and desing are choices.

I only see SQL view after the query is open as a choice.

[June7]

Ooops, sorry, that's right, forgot. Wow, this bites!

You experience this issue on other machines? If you want to provide db I will test in my install. Follow instructions at bottom of my post.

[wshshell]

I have the same problem and it started about two months ago. MSAccess 2010 on Win 7 64 bit. My work around is to go into the immediate window and type '? Application.CurrentDb.QueryDefs("your_query_name") .SQL' to get the SQL. From there I can rebuild the query quickly. It's not the best situation but I don't understand why it's occurring. I intend on fixing the SQL then saving it as text. Next time it happens I'll compare the SQL to see what changes. Perhaps a better option is to build the crosstab query on the fly and not bother saving it and having get corrupted each time.

[Henry Reardon]

Same problem/error message here...it would be easy to fix if the error message didn't lock up the query and close it out as soon as you click OK. Can't get to SQL View either. Yet another in the ever-expanding field of Microsoft landmines & bugs.