Nested Sql - "Syntax Error in From Clause"

[James]

Please help me with this sql:
SELECT z005aNORTHEAST_3days_Formula.Region,
z005aNORTHEAST_3days_Formula.DiffofClaimsAssignmen t
FROM (SELECT [test].Region, ([test].[Date to Service Provider])-(
[test].[Date Received By TNT]) AS DiffofClaimsAssignment
FROM
(SELECT [Claims Table 02_23_05].Region, [Claims Table 02_23_05].[Date
received by TNT], [Claims Table 02_23_05].[Date To Service Provider],
[Claims Table 02_23_05].[Date Acknowledged by DSP]
FROM [Claims Table 02_23_05]
WHERE ((([Claims Table 02_23_05].Region)="NORTHEAST"))) as test) AS
z005aNORTHEAST_3days_Formula
WHERE (((z005aNORTHEAST_3days_Formula.DiffofClaimsAssign ment)<=3 ));

Access XP and 2003 returned correct data and correct nbr of records.
After the query was saved and re-opened, it also provided correct data
and nbr of records. Yet, whenever I revisited the design view and
clicked on "Save", Access popped up an error msg: "Syntax Error in FROM
Clause". Currently, I have only 400 records from the source table and
the sql generates 4 rows of returned records. I do not know how to fix
it & could not understand why Access automatically changed the sql
after I reopend it (plz see below), but still show error msg. Thank you
in advance. James in Plano
SELECT z005aNORTHEAST_3days_Formula.Region,
z005aNORTHEAST_3days_Formula.DiffofClaimsAssignmen t
FROM [SELECT [test].Region, ([test].[Date to Service Provider])-(
[test].[Date Received By TNT]) AS DiffofClaimsAssignment
FROM
(SELECT [Claims Table 02_23_05].Region, [Claims Table 02_23_05].[Date
received by TNT], [Claims Table 02_23_05].[Date To Service Provider],
[Claims Table 02_23_05].[Date Acknowledged by DSP]
FROM [Claims Table 02_23_05]
WHERE ((([Claims Table 02_23_05].Region)="NORTHEAST"))) as test]. AS
z005aNORTHEAST_3days_Formula
WHERE (((z005aNORTHEAST_3days_Formula.DiffofClaimsAssign ment)<=3 ));

[billmiami2]

James,

This is likely the same nastly little problem that I've been struggling
with for years. Access insists on replacing the parentheses around
your subquery with a square bracket in front and a square
bracket/period on the end. If you have square brackets anywhere else
in your query, it's likely to fail. If you fix it by restoring the
query to its original state, Access will again replace the parentheses
the next time you edit the query.

What I normally do is I keep a good copy of the query in a text file.
Every time I need to change the query, I update the text file and paste
the results into the query designer (SQL view).

If someone knows a way to stop Access from rewriting these queries,
please tell us! I certainly hope that this problem is fixed in the
newest version of Access.

Bill E.
Hollywood, FL

[lesperancer]

can't you just save your subquery as a seperate query ?

[James]

Thanks, Bill.
I'll keep a good copy separately for future use. Wonder if Microsoft
would listen to their customers' complains [!?! :)]
Have a great Friday. James in Plano

bi********@netscape.net wrote:

James,

This is likely the same nastly little problem that I've been struggling
with for years. Access insists on replacing the parentheses around
your subquery with a square bracket in front and a square
bracket/period on the end. If you have square brackets anywhere else
in your query, it's likely to fail. If you fix it by restoring the
query to its original state, Access will again replace the parentheses
the next time you edit the query.

What I normally do is I keep a good copy of the query in a text file.
Every time I need to change the query, I update the text file and paste
the results into the query designer (SQL view).

If someone knows a way to stop Access from rewriting these queries,
please tell us! I certainly hope that this problem is fixed in the
newest version of Access.

Bill E.
Hollywood, FL

[James]

Surely can, but was hoping to run 1 query instead of many for the same
result.