Access Fields disapear in Query

[gheyman]

My fields disappeared in my query after I unchecked the Show box. I don't mean they don't show data view, I mean when I go back to the design view they are all gone.

What happened?

 

[xenou]

If you don't want to show the fields then they don't need to be there unless they are part of join criteria or where criteria. So that is probably expected behavior.

 

[Joe4]

If you have fields that are unmarked as "Not Show" that have Criteria under them, or if they are Calculated Fields that are used in other Calculations in the same query, I believe it should keep them.
However, if they are just straight Table fields, and they are not being used for Criteria, it would make sense that they disappear (as they are being displayed or used for anything in the query, there is no reason to include it, as Xenou said).

 

[gheyman]

I probably didn't explain it well enough. I have calculated fields

MONTH0_Active: IIf(([PDIR Due Date] Is Not Null) And ([PDIR Submission Date] Is Not Null),IIf((DateDiff("m",[PDIR Due Date],Now()))=0,1,IIf(((DateDiff("m",[PDIR Submission Date],Now()))>=0) And ((DateDiff("m",[PDIR Due Date],Now()))<0),1,0)),0)

Several like the above.

Before I ran the query I unchecked the Show box, because in this instance the data I just didn't want every field so show (I didn't want to keep scrolling to the right to find something). But the when I ran the query, those field disappeared from the query. They are gone. Even in Design view, all the fields I checked the boxes on are completely gone. I don't mean that are just not showing when I run the Query, they no longer exist. Poof!

 

[Joe4]

I probably didn't explain it well enough. I have calculated fields

OK, we didn't know that you were talking about calculated fields, but we did understand what you were saying, so what we said holds true.

It makes sense that it will drop those fields because they are not being used for anything in the query.
Why it does this makes more sense if you understand how Access and the Query Builder works.

Queries are really a form of SQL (or T-SQL) code. If you change your Query view to "SQL View", you can see the SQL code that it is building.
So the Query Builder is just a GUI interface that helps you to build the SQL code (if you are not a technical SQL writer and don't want to write the SQL code directly).
The following shows the structure of SQL Queries: http://www.mycms.ca/index.cfm/page/sqlqueries.html

The SELECT clause is all the fields that you want to show in the query. So anything marked "Show" will be put in this clause.
The FROM clause are the Tables/Queries that are used as data sources for the Query (the objects you add to it).
There could be a related JOIN clause, which is the established relationship between the Query objects (the joining lines in the Query Builder).
The WHERE clause is the Criteria. So anything place on the Criteria lines will go here.
The ORDER BY clause is how the query is sorted. So anything mark as "Sort" in the Query Builder will go here.

So, if you have any field (regardless of whether or not it is a Table field or Calculated field), in which Show is unchecked, Sort is empty, and Criteria is empty, it is not placed in any of the clauses of the SQL statement, and will therefore be dropped. So it makes sense why it is doing that.

I guess that leads to the question, if you are not using it for anything in the query, then why is it there in the first place? It doesn't appear to be needed for anything.

 

[gheyman]

Thanks, that makes sense. But I think its a crazy feature in Access. I like most would think that the fields just wouldn't "Show" when the query was ran. not that they would vanish from existence. I learned the hard way, I guess

 

[Joe4]

But I think its a crazy feature in Access

I don't think it really "is a feature" of Access, just more a functionality of how it all works. If it is all being stored as SQL code, there is nowhere to put it in the SQL code structure under those conditions.
The only way it would be able to store it is if it stored it in a format different than SQL (which could them make Access not compatible with SQL).
So I think it is more of a SQL restriction than an Access one.

The important thing to understand is that when you are building queries, you are really building SQL code. The Query Builder is just trying to give you a pretty GUI interface to do that. But when you open a query, you are really calling the SQL code behind it, I believe.

Note that if the calculations are something you want to keep, then don't uncheck that show box. Most of the time, you shouldn't really be concerned about that anyway. Most of the time, when presenting data to users, you are doing it through Forms and Reports. If you do ever have need to show members (or export) the query directly, and don't want to show certain fields (but want to keep those calculated fields), then just create a new query, using the other query as its source (making a "query of a query"), and only choose the fields that you want to see.

That way you still have the original with everything you need, and then hae a different view that only show them the information you want to show them.

 

[Micron]

Thanks, that makes sense. But I think its a crazy feature in Access. I like most would think that the fields just wouldn't "Show" when the query was ran. not that they would vanish from existence. I learned the hard way, I guess

You would have been OK if you didn't close the query and save your changes. Simply switching views would leave the field there, checked or not. Closing it, no. You can also set field widths to 0 for temporary viewing. The width setting will remain if you save the query with different field widths.