[Solved] Inconsistent Behaviour in SQL Syntax

Create a Query in design view which has the SQL -
This works in Tools >SQL and in a macro where this is the query statement. I was under the impression that Table and Field names need to be quoted (“TestTable1”) in the version of SQL used in Base (HSQLDB). I have found that this also works -
without any quotes which should not.
However an update using this SQL -
only works if the quotes are used. If they are left out the error is TestTable1 not found.
Looking at the information on the HSQLDB website on SQL it states the Table and Field names need to be quoted unless they are in block capitals. If the table used in the above update query is replaced by a table named TEST then the quotes can be left out.
So why does a Select work without quotes?
- Code: Select all Expand viewCollapse view
SELECT * FROM "TestTable1" WHERE "MY_ID" = 0
This works in Tools >SQL and in a macro where this is the query statement. I was under the impression that Table and Field names need to be quoted (“TestTable1”) in the version of SQL used in Base (HSQLDB). I have found that this also works -
- Code: Select all Expand viewCollapse view
SELECT * FROM TestTable1 WHERE MY_ID = 0
without any quotes which should not.
However an update using this SQL -
- Code: Select all Expand viewCollapse view
UPDATE "TestTable1" SET "Cust" = '0' WHERE "MY_ID" = 0
only works if the quotes are used. If they are left out the error is TestTable1 not found.
Looking at the information on the HSQLDB website on SQL it states the Table and Field names need to be quoted unless they are in block capitals. If the table used in the above update query is replaced by a table named TEST then the quotes can be left out.
So why does a Select work without quotes?