Re: Access doesn't like its own SQL

Thanks, Gunny. That worked a treat.

I guess Access' [%$##@_Alias] thing is like the 'AS DerivedTable' syntax
recommended in T-SQL.


"'69 Camaro" wrote:

Hi, Allen.

Any advice?

When editing a Jet-induced pain, replace the opening bracket and closing
bracket of the subquery with opening and closing parentheses, then assign
your own alias name (avoiding reserved words and illegal identifiers). For
example, change:

SELECT KitItem
FROM [SELECT KitItem
FROM SubKits
WHERE SubKits.ITEM = '600-211-1231']. AS [%$##@_Alias]
WHERE (DateCreated >= #12/1/2006#)

. . . to:

SELECT KitItem
FROM (SELECT KitItem
FROM SubKits
WHERE SubKits.ITEM = '600-211-1231') AS Alternator
WHERE (DateCreated >= #12/1/2006#)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


"Allen_N" wrote:

I'm trying to modify my original query, and I'm being plagued by this
auto-generated non-reusable alias rubbish again.

Stefan said it was fixed in Access 2003; but, I am using 2003, and it ain't
fixed!

Any advice?


"Stefan Hoffmann" wrote:

hi Allen,

Allen_N wrote:
Here's a weird one. The code

Set qy = CurrentDb.QueryDefs(strSourceQuery)
strSQL = qy.SQL

Set qy = CurrentDb.QueryDefs("Kits Found Special")
qy.SQL = strSQL

produces error 3131: "Syntax error in FROM clause", despite the fact that
the 1st query works fine if run from the database window.
(note that Access added the "]. AS [%$##@_Alias]" foolishness, not I).
This alias is need by Jet to run the query. Access use this not so nice
default alias, which is not reusable as you have noticed.

The simple solution is to use a extra query for your subselect.

btw, under Access 2003 this probem is fixed.

mfG
--> stefan <--

.

Relevant Pages

  • Re: Access doesnt like its own SQL
    ... No one in his right mind would pick such a bizarre alias for the subquery, ... Jet slaps it on to optimize the query. ... See http://www.QBuilt.com for all your database needs. ... FROM [SELECT KitItem ...
    (microsoft.public.access.modulesdaovba)
  • Re: group by
    ... I'll have to study up some more on the 'ON' and 'AS' syntax. ... The AS is optional and is only for defining an alias for the derived ... The alias is always mandatory, ... Jeff Kish ...
    (comp.databases.ms-sqlserver)
  • Re: E2K7 PF Add email alias
    ... Found the issue to be syntax. ... When using Set-MailPublicFolder I used ... where I should actually have been using the alias. ...
    (microsoft.public.exchange.admin)
  • Re: 9i: Check- constraint Upper(Column)
    ... the brackets are *not* optional. ... So it sounds to me as *alias* is a nice word for a bug. ... the table is referenced in the index definition (but I have ... An error 'missing brackets' is not appropriate here, for the syntax is ...
    (comp.databases.oracle.misc)
  • Re: percentages of top 10 tallies
    ... On Wed, 06 Oct 2004 15:51:16 GMT, Mortar wrote: ... If you use tables in the FROM clause, the syntax is: ... You don't have to provide an alias (in which case you can reference ...
    (microsoft.public.sqlserver.programming)