Syntax Error in From Clause

[CR]

Hi,

I have the follow query:

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN (SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Forms]![DateRange]!
[SpecificFromDate] & " 18:00:00" AND dateadd("d",1,[Forms]![DateRange]!
[SpecificToDate]) & " 17:59:59") AS BackupTracking ON
SchoolServers.ServerID = BackupTracking.ServerID) ON Schools.SchoolID
= SchoolServers.SchoolID
WHERE (((Schools.OptionA)=Yes))
ORDER BY Schools.SchoolName;

which works fine, however periodically I can not run the query nor
open it in design view as I get an error saying "Syntax Error in From
Clause". When this happens I have to create a new query, paste in the
code and delete the old and it works again. I have noticed that if I
save it and then open it again in design view the code has changed.
Access 2003 puts in [ instead of the ( after the INNER Join and a ].
instead of ) before the AS.

So why does access change the code which wont then save unless I
change it back and why does it periodically stop working?
Is the code wrong?

 

[Jerry Whittle]

First why the query changes. If you only stay in SQL view and save it there,
Access usually doesn't mess with it. However once you go to design view,
especially if you save it there, Access messes with it.

Once you run a query, Access optimizes who it's executed. Access might even
decide to change the joins to make it more efficient.

As for why it's breaking, there can be a few reasons.

1. I'm a little worried about the following part of the SQL statement. I'm
thinking that the asterisk * should be an actual field name.

2. Access could be changing the SQL statement badly.

3. There could be a corruption problem with the database file. I'd open up a
new database file and import everything from the original into it. See if
that fixes things.

 

[CR]

First why the query changes. If you only stay in SQL view and save it there,
Access usually doesn't mess with it. However once you go to design view,
especially if you save it there, Access messes with it.

Once you run a query, Access optimizes who it's executed. Access might even
decide to change the joins to make it more efficient.

As for why it's breaking, there can be a few reasons.

1. I'm a little worried about the following part of the SQL statement. I'm
thinking that the asterisk * should be an actual field name.

2. Access could be changing the SQL statement badly.

3. There could be a corruption problem with the database file. I'd open up a
new database file and import everything from the original into it. See if
that fixes things.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I have the follow query:

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN (SELECT
BackupTracking.* FROM BackupTracking
WHERE BackupTracking.Date BETWEEN [Forms]![DateRange]!
[SpecificFromDate] & " 18:00:00" AND dateadd("d",1,[Forms]![DateRange]!
[SpecificToDate]) & " 17:59:59") AS BackupTracking ON
SchoolServers.ServerID = BackupTracking.ServerID) ON Schools.SchoolID
= SchoolServers.SchoolID
WHERE (((Schools.OptionA)=Yes))
ORDER BY Schools.SchoolName;

which works fine, however periodically I can not run the query nor
open it in design view as I get an error saying "Syntax Error in From
Clause". When this happens I have to create a new query, paste in the
code and delete the old and it works again. I have noticed that if I
save it and then open it again in design view the code has changed.
Access 2003 puts in [ instead of the ( after the INNER Join and a ].
instead of ) before the AS.

So why does access change the code which wont then save unless I
change it back and why does it periodically stop working?
Is the code wrong?

Hi,

Thanks for the suggestions.

I'm not actually concerned about design view but I dont know how to
open the query straight in SQL view. The design button just opens it
(when it hasn't broke) and then I have to click the drop down arrow
to select SQL view. I have looked to see if I can open it straight in
SQL view but can't find a way. Is there?
I have been tweeking the queries which is why I have been changing
them.

1. The Astrix in BackupTracking.* FROM BackupTracking is to select all
fields in that table. Should I not do that?

2. Well it certainly seems that way. When I open the query and go to
the SQL view I can see it has put in the [] as described. If I dont
save it (it wont let me any way) and carry on running it it works OK.

3. This could be quite time consuming. The database is in two parts.
All the tables are in a backend, form and queries in the front with
linked tables. I have noticed that all the queries based on the one
shown, just tweeked for dates, stop working for the same reason.