Fields in Query disappear

[Don S]

I thought I was nuts until I watched it happen:

I have a crosstab query that uses 3 fields for filtering. One of them
is "TERRITORY" whose value is either "1" or "0". When I modified the
query to remove either criteria (I wanted all records regardless of
the TERRITORY value) and ran it, all worked fine. When I closed and
saved the query and then reopened it in design view, the TERRITORY
field was missing.

Where should I begin my search for the problem? I have been getting
irregular results in some queries & am wondering if this box is
haunted. Does Access have a "give me true results" box that I should
check? : )

Thanks to those who believe me and post some direction.

Don S

 

[Guest]

Try putting "TERRITORY" back in with criteria --

Like "*"

 

[Don S]

Karl,

That works. I had been putting something in the criteria just to keep
the field in the query. I guess Access figures if we don't use the
field, we don't need it. Sure makes it hard to keep track of how the
queries are supposed to be structured.

Thanks!

Don S

 

[John Spencer (MVP)]

If you don't have the field in the SELECT list (marked "Show") and you don't
apply any criteria against it, then Access assumes that you don't need the field
in your query and removes it from the SQL statement that the query grid generates.

If Access did not remove the field, the query would fail.

Workarounds: If the field always has a value then make the criteria the field name.

Field: Territory
Table: YourTableName
Show: Not Checked
Criteria:[YourTableName].[Territory]

 

[Don S]

Thanks John,

I suspected it was something like that. It's been 4 or 5 years since
I used Access extensively. Fortunately, it is coming back to me
faster than the first time I used it.

Thank You,
Don S

If you don't have the field in the SELECT list (marked "Show") and you don't
apply any criteria against it, then Access assumes that you don't need the field
in your query and removes it from the SQL statement that the query grid generates.

If Access did not remove the field, the query would fail.

Workarounds: If the field always has a value then make the criteria the field name.

Field: Territory
Table: YourTableName
Show: Not Checked
Criteria:[YourTableName].[Territory]

I thought I was nuts until I watched it happen:

I have a crosstab query that uses 3 fields for filtering. One of them
is "TERRITORY" whose value is either "1" or "0". When I modified the
query to remove either criteria (I wanted all records regardless of
the TERRITORY value) and ran it, all worked fine. When I closed and
saved the query and then reopened it in design view, the TERRITORY
field was missing.

Where should I begin my search for the problem? I have been getting
irregular results in some queries & am wondering if this box is
haunted. Does Access have a "give me true results" box that I should
check? : )

Thanks to those who believe me and post some direction.

Don S