Bug? Using full identity expression in any arbitrary column works

[Danny]

If I use the following expression as search criteria in ANY column of the
design grid, execution of the query will return correct results:

Year([DateReceived])=Year(Now())

Where [DateReceived] is the field name in the table included in the query
design.

So, If I put that expression in ANY column in the "Criteria" row, the query
will execute without error, and return the correct number of records from the
table.

This is very strange behavior - no?

 

[Jerry Whittle]

Not really. Open the query in SQL view and see what it says. Go back to
design view and modify it like your second example. Again open the SQL view.
I bet that it isn't too different.

 

[Danny]

Jerry,

Thanks for the reply.

There aren't two examples here - only one.

Another way of putting my question would be: Why in fact does Access take
this criteria in the design view, and translate it the way it does in the SQL
statement, as opposed to what you would EXPECT it to look like based on the
graphic design view?

If I put the expression 'Year([DateReceived])=Year(Now())' (without quotes)
in the Criteria row in the column [DepartmentName] (for example), then the
ACTUAL SQL statement shows:

Select...
From...
Where Year([DateReceived])=Year(Now())

The Where component makes no reference to the [DepartmentName] column.

I would EXPECT the SQL to read:

Select...
From...
Where [DepartmentName] = "Year([DateReceived])=Year(Now())"

Let me point out that I would never use such identity expressions (FieldName
= Criteria) as criteria - this came up by accident in teaching someone how to
use criteria expressions. But once this came up, the user is now convinced
that this construction for query criteria is actually CONVENIENT because now
they don't have to be careful as to what column in the query design grid they
place the criteria!

Not really. Open the query in SQL view and see what it says. Go back to
design view and modify it like your second example. Again open the SQL view.
I bet that it isn't too different.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

If I use the following expression as search criteria in ANY column of the
design grid, execution of the query will return correct results:

Year([DateReceived])=Year(Now())

Where [DateReceived] is the field name in the table included in the query
design.

So, If I put that expression in ANY column in the "Criteria" row, the query
will execute without error, and return the correct number of records from the
table.

This is very strange behavior - no?

 

[John Spencer]

The query grid was coded well enough to see that expression as what is it and
properly handle it when constructing the string. If you save that
construction, close the query, and reopen it the query grid will look just as
if you had done this the "proper" way.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Jerry,

Thanks for the reply.

There aren't two examples here - only one.

Another way of putting my question would be: Why in fact does Access take
this criteria in the design view, and translate it the way it does in the SQL
statement, as opposed to what you would EXPECT it to look like based on the
graphic design view?

If I put the expression 'Year([DateReceived])=Year(Now())' (without quotes)
in the Criteria row in the column [DepartmentName] (for example), then the
ACTUAL SQL statement shows:

Select...
From...
Where Year([DateReceived])=Year(Now())

The Where component makes no reference to the [DepartmentName] column.

I would EXPECT the SQL to read:

Select...
From...
Where [DepartmentName] = "Year([DateReceived])=Year(Now())"

Let me point out that I would never use such identity expressions (FieldName
= Criteria) as criteria - this came up by accident in teaching someone how to
use criteria expressions. But once this came up, the user is now convinced
that this construction for query criteria is actually CONVENIENT because now
they don't have to be careful as to what column in the query design grid they
place the criteria!

Not really. Open the query in SQL view and see what it says. Go back to
design view and modify it like your second example. Again open the SQL view.
I bet that it isn't too different.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

If I use the following expression as search criteria in ANY column of the
design grid, execution of the query will return correct results:

Year([DateReceived])=Year(Now())

Where [DateReceived] is the field name in the table included in the query
design.

So, If I put that expression in ANY column in the "Criteria" row, the query
will execute without error, and return the correct number of records from the
table.

This is very strange behavior - no?

 

[Clifford Bass]

Hi Danny,

That is indeed odd. I would actually expect:

Select...
From...
Where [DepartmentName] = (Year([DateReceived])=Year(Now()))

Which should then compare DepartmentName with either True or False
depending on the DateReceived and Now() values. But it does not do that
either.

Clifford Bass