Syntax error in FROM clause

  • Thread starter Thread starter antonette
  • Start date Start date
A

antonette

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];

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!
 
For a start, I would get rid of all of the special characters in your alias
( [%$##@Alias]).
 
antonette said:
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];
Click to expand...


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.
 
Thanks for your help! If I change your query a bit (the GROUP BY
should've been within the parentheses) I can get it to work. This
happens to be the query I finally used:

SELECT Sum([Temp_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 [Temp_Alias];

The original bit had those characters since that's what Access
defaulted to -- but I hadn't been able to go back and edit without
gettings those syntax errors.
 
Back
Top