Access 2003 keeps corrupting query definitions

N

Nick O

Trying to query on a subquery.

Text works fine but when I return to it, the query designer
tries replace the parentheses () with [] around the subquery and sticks a
'.' after the ']'. A variant is it creates a bizarre alias e.g.
[%$##@_Alias] after the subquery.

Example:

SELECT *
FROM (SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region");

which runs fine, becomes:

SELECT *
FROM [SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region"]. AS [%$##@_Alias];

when reloaded.

Any ideas - it's driving me nuts!
 
C

Chaim

You don't close your string where it says:
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
Should read"
'' as route", 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from

Good Luck!
 
N

Nick O

Thanks for the reply but not quite: that's two single quotes not a
double-quote so the string does not need closing i.e. it's returning empty
string as route.

Cheers,

Nick
Chaim said:
You don't close your string where it says:
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
Should read"
'' as route", 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from

Good Luck!
--
Chaim

Nick O said:
Trying to query on a subquery.

Text works fine but when I return to it, the query designer
tries replace the parentheses () with [] around the subquery and sticks a
'.' after the ']'. A variant is it creates a bizarre alias e.g.
[%$##@_Alias] after the subquery.

Example:

SELECT *
FROM (SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region");

which runs fine, becomes:

SELECT *
FROM [SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region"]. AS [%$##@_Alias];

when reloaded.

Any ideas - it's driving me nuts!
 
C

Chaim

Nick,

Just as a question: Why are you doing a 'SELECT *' from the UNION query? Why
not simply execute the UNION query?

Nick O said:
Thanks for the reply but not quite: that's two single quotes not a
double-quote so the string does not need closing i.e. it's returning empty
string as route.

Cheers,

Nick
Chaim said:
You don't close your string where it says:
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
Should read"
'' as route", 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from

Good Luck!
--
Chaim

Nick O said:
Trying to query on a subquery.

Text works fine but when I return to it, the query designer
tries replace the parentheses () with [] around the subquery and sticks a
'.' after the ']'. A variant is it creates a bizarre alias e.g.
[%$##@_Alias] after the subquery.

Example:

SELECT *
FROM (SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region");

which runs fine, becomes:

SELECT *
FROM [SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region"]. AS [%$##@_Alias];

when reloaded.

Any ideas - it's driving me nuts!
 
N

Nick O

This was by nature of an example showing the symptoms. The actual query is
an aggregation and some processing on top of the union.

However, once this bug creeps in, it seems to strike at the query no matter
how simple you make it.

My question is: has anyone seen this behaviour before, and how did they fix
it?

Brgds,

Nick

Chaim said:
Nick,

Just as a question: Why are you doing a 'SELECT *' from the UNION query? Why
not simply execute the UNION query?

Nick O said:
Thanks for the reply but not quite: that's two single quotes not a
double-quote so the string does not need closing i.e. it's returning empty
string as route.

Cheers,

Nick
Chaim said:
You don't close your string where it says:
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
Should read"
'' as route", 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from

Good Luck!
--
Chaim

Trying to query on a subquery.

Text works fine but when I return to it, the query designer
tries replace the parentheses () with [] around the subquery and sticks a
'.' after the ']'. A variant is it creates a bizarre alias e.g.
[%$##@_Alias] after the subquery.

Example:

SELECT *
FROM (SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination
region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region");

which runs fine, becomes:

SELECT *
FROM [SELECT [3a].Period, [3a].Origin, [3a].[Destination Region],
[3a].route, [3a].['Dest Regn Count'], [3b].seats, [3b].flights
FROM 3a INNER JOIN 3b ON ([3a].Period=[3b].Period) AND
([3a].Origin=[3b].Origin) AND ([3a].[Destination Region]=[3b].[Destination
Region]) AND ([3a].route=[3b].route)
UNION ALL select '' as period, '' as origin, q.data as [destination
region],
'' as route, 0 as ['Dest Regn Count'], 0 as seats, 0 as flights from
[~padvalues] q
where q.fieldname="Region"]. AS [%$##@_Alias];

when reloaded.

Any ideas - it's driving me nuts!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top