Re: "Identical" query SQL Throws Error

Larry, I can only guess that the issue was one of the many bugs in the JET
query engine, so I can't pinpoint it for you.

Microsoft seems to be uninterested in fixing these JET bugs. Naturally
enough, each new version has its own bugs, so when these are added and the
old ones are not fixed, the very engine that Access runs on is becoming more
and more unstable.

For examples of some of those I have personally struck, see:
http://allenbrowne.com/tips.html#Bug%20Engine

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LarryP" <LarryP@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B95DEC8E-8DF4-469F-91CB-AE7480B27CFC@xxxxxxxxxxxxxxxx
First, Allen, thanks for your interest and persistence in trying to help
me
through this.

In the course of yesterday I made a change that resulted in the second
query
(strSQL2) running correctly, but I'm completely sure why, so if you have
time, maybe you can explain it to me and others who might encounter
something
similar.

As you saw, in the second query I was trying to set up a filter on the
MYear
value (e.g., 2006) to be sure I was only getting one year's worth of data.
The second query of course got that value from the first query, and the
first
query got it from tblSPIData. My "fix" involved adding MasterCal back
into
the second query as a left join to tblSPIData, and setting up the filter
to
use the MYear value from MasterCal rather than the one in tblSPIData.
Presto, Jet was happy and the query ran as intended. Go figure. I can't
understand how that added anything significant to the earlier SQL
statement,
but obviously Jet did.

If you can shed any light, it'd be welcome. "It works now!" is good, "It
works now because...!" is better.


.

Relevant Pages

  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... subquery, but it's curious... ... repeated trips through the Query Editor's "graphical" side did ... The Jet Expression Evaluator (what functions & stuff are actually ... within brackets within the query. ...
    (microsoft.public.access.queries)
  • Re: SQL Query between 2 dates
    ... When you run this as a query, JET calls the Expression Service which figures out the values and substitutes them, so the query runs. ... When you include literal date values in a string, they must be delimited with #, and they should be formatted in the native JET format The Formatfunction call performs this operation. ... Dim rs AS DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Official Status of SQLServer 2005 ADP
    ... "remains with Jet and linked tables or remains with Jet ... but go with SQL pass-through queries and unbound forms" ... RecordSource is a query with a where clause that limits the number of rows ... > queries under MDB was bad and worst than the one offered by ADP while you ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Sometimes the JET optimizer does not run the subquery to completion. ... Problem is in the ORDER BY statement of the problem query below ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • Re: Rediculous query run tim
    ... While Jet optimizer performs well in some situation, ... another db engine, neither between version, of the same engine, since the ... to a field turns it into a computed expression) LAST. ... than it would be by creating it in a new field in an intermediate query. ...
    (microsoft.public.access.queries)