Show/Hide malfunction in Query ??

[stevekos]

Hello all,

I am fairly new to Access, and am still studying basic to intermediate level. I have come across an anomaly which is driving me crazy. I have a strange malfunction occuring in my queries. If I click to hide a field in a query it still shows up in the resulting query table when run, but the field is then totally hidden in the design view:

The first image is the query which has the 'show/hide' box ticked in the query's 'PhoneNumber' field to 'hide', but it results in hiding the field in the design view, not the resulting query table.



Any ideas what is going on here?

[CJ_London]

I suspect you either have a phonenumber column way off to the right and off the screen or you have hidden the column in the query grid by reducing the width of the column to nothing.

If you save and close the query and then reopen it in design view it should tidy up the grid.

Also, if this does not solve the problem, suggest you go to the sql window and see what is there (You may have 'SELECT *, ...) which selects all columns, although I would then have expected to see duplicates of all the columns

[ItsMe]

My vote is the Query Object is corrupt. I would copy the SQL from SQL View and paste it into notepad. Then, delete the Query Object, Compact and Repair the DB, and rebuild the Query Object.

[June7]

Checking the box means to show the field. Unchecking means to not show. A field that is unchecked and has no filter criteria will be dropped from the grid.

My vote is on * wildcard is still pulling the field into the query datasheet view. I just tested this and the fields are not duplicated. Look at SQL View and see if the * is used. The query builder will automatically use the * unless you set Access options to not do that.

File > Options > Object Designers > Query Design > check/uncheck Output all fields

So there is no malfunction, just preference settings and query syntax.

[stevekos]

Thanks June7, sorry for the slow reply, I have been working on some other projects. Your solution seems to have fixed the problem. I will have to remember to uncheck that option in all my databases though. Can it be set as a default setup in all new databases?

[stevekos]

Sorry for the double post. Unchecking the "output all fields" option on the database in question seems to have made it a default option on any new (or existing) databases. Thanks.

[June7]

Yes, that is an application, not database, option setting.