Re: Syntax error in FROM clause
From: Chris2 (indanthrene.NOTVALID_at_GETRIDOF.yahoo.com)
Date: 03/18/04
- Next message: Duane Hookom: "Re: CrossTab Query"
- Previous message: Buell: "calculating age in a query"
- In reply to: antonette: "Syntax error in FROM clause"
- Next in thread: antonette: "Re: Syntax error in FROM clause"
- Reply: antonette: "Re: Syntax error in FROM clause"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 18 Mar 2004 01:31:17 GMT
"antonette" <antonette658@hotmail.com> wrote in message
news:798ee48a.0403171140.65474a72@posting.google.com...
> Are there hidden characters added by Acess into queries?
>
> I am trying to return the sum of the [sell price] column created by a
> temporary table. I'm using Access 2000, Win2k, and generated the
> following syntax by using the design grid and typing into the SQL
> window:
>
> SELECT Sum([%$##@_Alias].sumsell) AS SumOfsumsell
> FROM [SELECT [Bangkok Purchase Order].[sell price] as sumsell,
> [Bangkok Purchase Order].bycore
> FROM [Bangkok Purchase Order]
> WHERE ((([Bangkok Purchase Order].poid)=58))
> GROUP BY [Bangkok Purchase Order].[sell price], [Bangkok Purchase
> Order].bycore]. AS [%$##@_Alias];
>
SELECT Sum([%$##@_Alias].sumsell) AS SumOfsumsell
FROM [SELECT [Bangkok Purchase Order].[sell price] as sumsell
,[Bangkok Purchase Order].bycore
FROM [Bangkok Purchase Order]
WHERE ((([Bangkok Purchase Order].poid)=58))
GROUP BY [Bangkok Purchase Order].[sell price]
,[Bangkok Purchase Order].bycore]. AS [%$##@_Alias];
Antonette,
The derived table on the FROM clause (the SELECT statement written on the
FROM clause) does not appear to be closed, that is, there is a starting "[",
but no closing "]" at the end.
I too would not use special characters on a Column-name alias (or spaces
in Column-names or Table-names, either).
If I wrote it, it would look like this (I didn't alter names, because I
don't know that you can).
Most changes are to "()" and "[]". I removed all the () around the single
WHERE clause criteria, and now there is a single pair of () around the
entire SELECT on the FROM clause.
I also removed the "." that appeared in the GROUP BY clause.
Note: This is not tested.
SELECT Sum([%$##@_Alias].sumsell) AS SumOfsumsell
FROM (SELECT [Bangkok Purchase Order].[sell price] as sumsell
,[Bangkok Purchase Order].bycore
FROM [Bangkok Purchase Order]
WHERE [Bangkok Purchase Order].poid = 58)
GROUP BY [Bangkok Purchase Order].[sell price]
,[Bangkok Purchase Order].bycore] AS [%$##@_Alias];
The reason that you are getting an error upon editing a working query is
because Access has the wonderful habit of changing the "()" and "[]" in a
query against your will (and other things). It does this after a Query is
closed, and then the Query keeps working, but should the Query be Opened and
any part of it be edited, Access begins to throw fits by refusing to believe
its a working Query. I have no idea why this massive software flaw exists,
but it's there. If you need to go back and successfully change a Query that
Access has broken in this manner, you need to clean up Access's improper
changes (fixing the SQL manually, character by character), so that it's a
working and saveable Query again, and then proceed to make whatever changes
you need.
Now you know why the Guru's of Access keep chanting, "No spaces or special
characters in Column or Table names." (Although avoiding their use will not
prevent this particular bug from striking: Using BETWEEN on the FROM clause,
and linking to Tables in other Databases by using a Path Name directly on
the FROM clause can both cause this error; sometimes the error is so severe
that you are not allowed back into the Query at all, at this point, you must
use the Visual Basic Editor to print out the SQL property in the Immediate
Window, delete the malfunctioning Query entirely, recreate it, and paste the
retrieved SQL back into it (and fixing it by hand, too, as mentioned above.
Oh, this is Access 2000 SP-3: I haven't seen the most severe form of this
bug, not being let back into the Query, since SP-1 and before.)
Sincerely,
Chris O.
> This returns the desired result. If I go back to it however, and
> simply add a space to the end of the first line and delete it, I get a
> "Syntax error in FROM clause" and it highlights the second FROM. All
> the text remains the same.
>
> Does anyone know what this could be? Or maybe there's better syntax I
> can use?
> Any help would be greatly appreciated!
- Next message: Duane Hookom: "Re: CrossTab Query"
- Previous message: Buell: "calculating age in a query"
- In reply to: antonette: "Syntax error in FROM clause"
- Next in thread: antonette: "Re: Syntax error in FROM clause"
- Reply: antonette: "Re: Syntax error in FROM clause"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
