From clause keeps breaking

[Guest]

I am trying to reproduce a report I have had working for years in crystal
reports. The report required three instances of the same table to make it
work.

When I creat the query in report designe and run it the query works. But
after saving the report when I go back in it says there is a syntax error in
the FROM caluse.

How can this be when it worked when I first created the query. I am running
Access 2000 and all the tables are linked into Access and are not actual
Access tables.

Anybody got any iseas?

 

[Gary Walter]

I am trying to reproduce a report I have had working for years in crystal
reports. The report required three instances of the same table to make it
work.

When I creat the query in report designe and run it the query works. But
after saving the report when I go back in it says there is a syntax error
in
the FROM caluse.

How can this be when it worked when I first created the query. I am
running
Access 2000 and all the tables are linked into Access and are not actual
Access tables.

Hi Keith,

Are you trying to use a subquery as a virtual table
in the FROM clause?

Did you add some field in that subquery that requires
brackets around it name?

If so, the "parser" on save will object to any further
brackets in the subquery because it wants to surround
the virtual table in brackets with an ending period.

That might be a possible cause?

good luck,

gary

 

[Keith Wilby]

I am trying to reproduce a report I have had working for years in crystal
reports. The report required three instances of the same table to make it
work.

When I creat the query in report designe and run it the query works. But
after saving the report when I go back in it says there is a syntax error
in
the FROM caluse.

How can this be when it worked when I first created the query. I am
running
Access 2000 and all the tables are linked into Access and are not actual
Access tables.

Anybody got any iseas?

Care to post your SQL code?

 

[Guest]

No, there are no subqueries. I linked to the table three times so I have
tables called ndmas, ndmas1 and ndmas2

 

[John W. Vinson]

I am trying to reproduce a report I have had working for years in crystal
reports. The report required three instances of the same table to make it
work.

When I creat the query in report designe and run it the query works. But
after saving the report when I go back in it says there is a syntax error in
the FROM caluse.

How can this be when it worked when I first created the query. I am running
Access 2000 and all the tables are linked into Access and are not actual
Access tables.

Anybody got any iseas?

One thing to try: if the query works when you enter it in the SQL window, save
it *from the SQL window* and don't open it in Query Design view at all. For
some complex queries, access jumbles the query badly to make it fit the query
grid, but if you never open the query grid, it may work.

John W. Vinson [MVP]

 

[Chris2]

I am trying to reproduce a report I have had working for years in crystal
reports. The report required three instances of the same table to make it
work.

When I creat the query in report designe and run it the query works. But
after saving the report when I go back in it says there is a syntax error in
the FROM caluse.

How can this be when it worked when I first created the query. I am running
Access 2000 and all the tables are linked into Access and are not actual
Access tables.

Anybody got any iseas?

Keith,

I too use MS Access 2000 (still).

I will echo the other comments here.

On some occasions, you can save a query, and MS Access will alter the
original SQL of the query when you save it.

You can run the query over and over again, and it works every time
despite the unwanted change that was made by MS Access (somehow, the
change exists, but doesn't take effect).

MS Access will ignore the alteration until you open the query again
and attempt to make a change (any change), at this point when you try
to save again, the alteration MS Access made earlier will now rise up
and report itself as an error.

You must determine what change MS Access made, and undo it manually.
When you do, your query will save again and should start working
(subject to the next time you open it and attempt to make a change).

If you are unable to identify what change MS Access made, you will
need to redevelop the query.


Sincerely,

Chris O.