473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Nested Sql - "Syntax Error in From Clause"

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

Nov 13 '05 #1
4 5128
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

Nov 13 '05 #2
can't you just save your subquery as a seperate query ?

Nov 13 '05 #3
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


Nov 13 '05 #4
Surely can, but was hoping to run 1 query instead of many for the same
result.

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Larry Bates | last post by:
It doesn't happen often, but once in a while I will introduce a syntax error into a Python program I'm working on (in IDLE) and doing File-Check responds with the expected "Failed to check, syntax...
1
by: mlke | last post by:
I would like to select the top 10 record from a table? How can I do it? In MS SQL, it's easy using select top 10 from table1. But in oracle database, I can't use top, anyone have any suggestions?...
10
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for database 'tempdb' is full. Back up the transaction...
1
by: Amir | last post by:
Hi all, I have a table called PTRANS with few columns (see create script below). I have created a view on top that this table VwTransaction (See below) I can now run this query without a...
2
by: P. Adhia | last post by:
Hello All, I am trying to redirect-restore a backup of a DB2 V6.x system (AIX 4.x) on DB2 V8.2 system (AIX 5.2). I get the above error with no other details. db2diag.log has a series of error...
2
by: j-in-uk | last post by:
Hi, I seem to get the error on DataRelation r2, the values are unique so I don't know why I am getting the error. Any sugesstions? DataRelation r1 = ds.Relations.Add(dtContinent_ContinentID,...
1
by: sesmap | last post by:
I am not certain if I set this up correctly - all help would be appreciated: SELECT .ACCOUNTID, .PUBCODE, .NAME, .PIATYPE, .FOD, .SUBSLNGTH, .RATECODE, .CURRENTBAL, .EOGDATE, .TELEPHONE,...
1
by: samneil | last post by:
I have 1 Database( MS Access) with 9 tables and 6 queries, Now I'm trying to view my 2 different tables on my VB6.0 using 2 Datagrids and 2"adodc".... here are the names of my two tables:...
1
by: canugi | last post by:
I need to store the contents of an SQL "in clause" in an MS Access 2000 table. I use MS Access version 9.0.8960 (SP3) This is my statement (and it works fine with the explicit "in clause"...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.