Access 2000 adds brackets [] to queries

[Eric Ellsworth]

Hi all,
Does anyone have any bright ideas for Access' tendency to add square
brackets when it parses queries, then tell you that the query syntax is
invalid. In my case, I'm trying to do a LEFT JOIN on a subquery, like so:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN (SELECT [Marker-Label] FROM targetMarkers
WHERE targetID = Forms!frmTargetDetails ) as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));

When I save the above query it gets parsed and saved as:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN [SELECT [Marker-Label] FROM targetMarkers
WHERE targetID = Forms!frmTargetDetails ]. as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));
or worse, it inserts the square brackets into the middle of the
subquery, like
[SELECT [Marker-Label] FROM targetMarkers WHERE targetID =
Forms]!frmTargetDetails )
If the query is saved as a QueryDef, Access seems to run the compiled
query and the misplaced text doesn't matter. However, if the original
SQL is used as the RowSource of a ComboBox, the combo box always given
an error OnEnter.
Is there a way to prevent Access from adding these erroneous square
brackets?

Thanks,

Eric

[Dimitri Furman]

On Jun 07 2004, 12:07 pm, Eric Ellsworth <e@e> wrote in
news:sZ********************@speakeasy.net:

Does anyone have any bright ideas for Access' tendency to add
square
brackets when it parses queries, then tell you that the query syntax
is invalid. In my case, I'm trying to do a LEFT JOIN on a subquery,
like so:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN (SELECT [Marker-Label] FROM
targetMarkers WHERE targetID = Forms!frmTargetDetails ) as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));

When I save the above query it gets parsed and saved as:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN [SELECT [Marker-Label] FROM
targetMarkers WHERE targetID = Forms!frmTargetDetails ]. as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));

<snip>

Is there a way to prevent Access from adding these erroneous square
brackets?

Access uses the undocumented [SELECT ... ]. AS syntax to support derived
tables in Jet SQL. While Jet itself is usually ok with that syntax, the
Access query editor gets thoroughly confused when it sees that syntax and
other square brackets in the same SQL string. Unlike Jet 3.5, Jet 4
supports the standard (SELECT ...) AS syntax for derived tables, which is
why your SQL compiles fine, but apparently some code behind Access query
editor insists on rewriting the SQL using the old proprietary syntax.

As far as workarounds, you may consider getting rid of hyphens in table and
column names, so that there are no other brackets besides the []. ones to
confuse Access. You may also try to use another variation of the same
undocumented syntax:
`SELECT ...`. AS (backticks instead of square brackets).

--
remove a 9 to reply by email

[Alan Webb]

Eric,
Access does this when table & column names violate ANSI SQL naming rules.
It adds brackets around table & column names so users can cheat and include
spaces & punctuation in table & column names when they shouldn't do that.
What you should do is get in the habit of always typing SQL in caps and
using the underscore (_) character as a wordspace. But you or whomever
built the database didn't do that and now you have brackets. The pain of
renaming every object in the database and fixing every bit of SQL that
references the renamed object may not be worth just putting up with the
presence of the brackets. Without knowing much more about your schema, I'd
suggest leaving things alone.

"Eric Ellsworth" <e@e> wrote in message
news:sZ********************@speakeasy.net...

Hi all,
Does anyone have any bright ideas for Access' tendency to add square
brackets when it parses queries, then tell you that the query syntax is
invalid. In my case, I'm trying to do a LEFT JOIN on a subquery, like so:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN (SELECT [Marker-Label] FROM targetMarkers
WHERE targetID = Forms!frmTargetDetails ) as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));

When I save the above query it gets parsed and saved as:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN [SELECT [Marker-Label] FROM targetMarkers
WHERE targetID = Forms!frmTargetDetails ]. as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));
or worse, it inserts the square brackets into the middle of the
subquery, like
[SELECT [Marker-Label] FROM targetMarkers WHERE targetID =
Forms]!frmTargetDetails )
If the query is saved as a QueryDef, Access seems to run the compiled
query and the misplaced text doesn't matter. However, if the original
SQL is used as the RowSource of a ComboBox, the combo box always given
an error OnEnter.
Is there a way to prevent Access from adding these erroneous square
brackets?

Thanks,

Eric

[Eric Ellsworth]

Thanks to both of you, Alan and Dimitri.

I will use the backticks, because I believe MySQL, which we are
eventually porting to, supports this syntax. That should solve my
problem nicely.

Again, thanks a lot.

Cheers,

Eric

Dimitri Furman wrote:

On Jun 07 2004, 12:07 pm, Eric Ellsworth <e@e> wrote in
news:sZ********************@speakeasy.net:

Does anyone have any bright ideas for Access' tendency to add
square
brackets when it parses queries, then tell you that the query syntax
is invalid. In my case, I'm trying to do a LEFT JOIN on a subquery,
like so:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN (SELECT [Marker-Label] FROM
targetMarkers WHERE targetID = Forms!frmTargetDetails ) as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));

When I save the above query it gets parsed and saved as:

SELECT DISTINCT [Marker-Values].[Marker-Label]
FROM [Marker-Values] LEFT JOIN [SELECT [Marker-Label] FROM
targetMarkers WHERE targetID = Forms!frmTargetDetails ]. as TM
ON [Marker-Values].[Marker-Label] = targetMarkers.[Marker-Label]
WHERE (((targetMarkers.[Marker-Label]) Is Null));

<snip>

Is there a way to prevent Access from adding these erroneous square
brackets?

Access uses the undocumented [SELECT ... ]. AS syntax to support derived
tables in Jet SQL. While Jet itself is usually ok with that syntax, the
Access query editor gets thoroughly confused when it sees that syntax and
other square brackets in the same SQL string. Unlike Jet 3.5, Jet 4
supports the standard (SELECT ...) AS syntax for derived tables, which is
why your SQL compiles fine, but apparently some code behind Access query
editor insists on rewriting the SQL using the old proprietary syntax.

As far as workarounds, you may consider getting rid of hyphens in table and
column names, so that there are no other brackets besides the []. ones to
confuse Access. You may also try to use another variation of the same
undocumented syntax:
`SELECT ...`. AS (backticks instead of square brackets).