Invalid bracketing of name / subquery problem - PLEASE HELP!

[Andrew McNab]

Hi folks,

I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.

Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values (a number reflecting number of hours
of time zone difference) to ALL timestamp-related records read in the
'main' part of the query, i.e.:

* Get a series of records from table A, inner joining on B & C to get
some extra data.
* Read the time zone difference factor (call it 'tzd') from
'Locations' ONCE.
* Apply 'tzd' to all timestamp-related fields retrieved from A, B and
C - i.e. A.StartTime = A.StartTime + tzd

Now, I believe the logical way to do this is to obtain the tzd factor
from 'Locations' as a subquery after my FROM clause, therefore
assuring that this operation only occurs once and does not
unnecessarily clog up my query.

So, I have ended up with a query like this - which works perfectly
fine I might add:

SELECT a.v1, a.v2, DateAdd("h", Locations.[Time Difference], a.[t1])
AS v3, b.v1...
FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc
WHERE ...

Note the subquery occurs during the FROM clause!

I can then run and save this query to my hearts content, and use it in
whatever reports I choose. All is fine.

Now, THE PROBLEM IS...

If I come to edit (add/remove fields to/from) this query again in
Access Query Design or raw SQL view, and then go to save the query /
update the Record Source property, I get this error:

"Invalid bracketing of name 'SELECT [Location Name'."

Investigations reveal that the error is caused because Access decides
to do the bloody "smart" thing and reformat my query in it's own
image, so that the part of the query which once read:

FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc

Now reads...

FROM [SELECT [Location Name], [Time Difference] FROM Locations ml
WHERE [Location Name] = [Enter the name of your current location]]. AS
myLoc, a INNER JOIN (b INNER JOIN c ON ...) ON ...

Digging the newsgroups has revealed that Access does not like
occurences of double brackets ('[[' or ']]') - yet the stupid thing
still decides to do it!

Now, I can solve this problem by simply replacing the '[SELECT' with
'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,

AND HERE IS MY QUESTION:

Surely there has to be a better way around this? Is this solvable, or
am I stuck with this workaround?

I really do not want to have to rewrite this query because:

* This type of logic/structure is used in 30+ different queroes.
* I cannot see how else I can run a subquery to get this information,
and don't see how a LEFT JOIN or other JOIN type can help me obtain
the data (tzd) that I need without 'affecting' the data I return!

And just to finish off... can I please go back to SQL Server now?
I've learnt my lesson!

Apologies for waffling on, if ANYONE can help, I will be eternally
greatful.

Regards,

Andy McNab

[Phil Stanton]

Although it dosn't help, to my mind it is best not to have spaces in any
access objects. This gets over all the sqare bracket problems- you just
don't need them
TimeDifference instead of [Time Difference]
LocationName instead of [Location Name] etc
Incidently what is Locations ml in your code

"Andrew McNab" <an**********@ptc.pilkington.co.uk> wrote in message
news:6d**************************@posting.google.c om...

Hi folks,

I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.

Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values (a number reflecting number of hours
of time zone difference) to ALL timestamp-related records read in the
'main' part of the query, i.e.:

* Get a series of records from table A, inner joining on B & C to get
some extra data.
* Read the time zone difference factor (call it 'tzd') from
'Locations' ONCE.
* Apply 'tzd' to all timestamp-related fields retrieved from A, B and
C - i.e. A.StartTime = A.StartTime + tzd

Now, I believe the logical way to do this is to obtain the tzd factor
from 'Locations' as a subquery after my FROM clause, therefore
assuring that this operation only occurs once and does not
unnecessarily clog up my query.

So, I have ended up with a query like this - which works perfectly
fine I might add:

SELECT a.v1, a.v2, DateAdd("h", Locations.[Time Difference], a.[t1])
AS v3, b.v1...
FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc
WHERE ...

Note the subquery occurs during the FROM clause!

I can then run and save this query to my hearts content, and use it in
whatever reports I choose. All is fine.

Now, THE PROBLEM IS...

If I come to edit (add/remove fields to/from) this query again in
Access Query Design or raw SQL view, and then go to save the query /
update the Record Source property, I get this error:

"Invalid bracketing of name 'SELECT [Location Name'."

Investigations reveal that the error is caused because Access decides
to do the bloody "smart" thing and reformat my query in it's own
image, so that the part of the query which once read:

FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc

Now reads...

FROM [SELECT [Location Name], [Time Difference] FROM Locations ml
WHERE [Location Name] = [Enter the name of your current location]]. AS
myLoc, a INNER JOIN (b INNER JOIN c ON ...) ON ...

Digging the newsgroups has revealed that Access does not like
occurences of double brackets ('[[' or ']]') - yet the stupid thing
still decides to do it!

Now, I can solve this problem by simply replacing the '[SELECT' with
'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,

AND HERE IS MY QUESTION:

Surely there has to be a better way around this? Is this solvable, or
am I stuck with this workaround?

I really do not want to have to rewrite this query because:

* This type of logic/structure is used in 30+ different queroes.
* I cannot see how else I can run a subquery to get this information,
and don't see how a LEFT JOIN or other JOIN type can help me obtain
the data (tzd) that I need without 'affecting' the data I return!

And just to finish off... can I please go back to SQL Server now?
I've learnt my lesson!

Apologies for waffling on, if ANYONE can help, I will be eternally
greatful.

Regards,

Andy McNab

[Steve Jorgensen]

FYI, it's pretty amazing that Access will convert your query into its own
undocumented syntax for the same thing at all. What you describe is, to my
knowledge, only referred to as a subquery in Oracle parlance, and is referred
to as a declared table in MS SQL Server parlance. In Access, it has no
terminology because it's not documented to exist, though it is used by some of
the Access wizards.

In any case, the Access form of the declared table (or whatever it is) does
not support bracketted expressions within the inner select, so it cannot be
used with table/column names that require brackets such as those with spaces,
etc. This is one of the many reasons to avoice spaces in object names in
Access.

On 1 Dec 2003 14:11:13 -0800, an**********@ptc.pilkington.co.uk (Andrew McNab)
wrote:

Hi folks,

I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.

Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values (a number reflecting number of hours
of time zone difference) to ALL timestamp-related records read in the
'main' part of the query, i.e.:

* Get a series of records from table A, inner joining on B & C to get
some extra data.
* Read the time zone difference factor (call it 'tzd') from
'Locations' ONCE.
* Apply 'tzd' to all timestamp-related fields retrieved from A, B and
C - i.e. A.StartTime = A.StartTime + tzd

Now, I believe the logical way to do this is to obtain the tzd factor
from 'Locations' as a subquery after my FROM clause, therefore
assuring that this operation only occurs once and does not
unnecessarily clog up my query.

So, I have ended up with a query like this - which works perfectly
fine I might add:

SELECT a.v1, a.v2, DateAdd("h", Locations.[Time Difference], a.[t1])
AS v3, b.v1...
FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc
WHERE ...

Note the subquery occurs during the FROM clause!

I can then run and save this query to my hearts content, and use it in
whatever reports I choose. All is fine.

Now, THE PROBLEM IS...

If I come to edit (add/remove fields to/from) this query again in
Access Query Design or raw SQL view, and then go to save the query /
update the Record Source property, I get this error:

"Invalid bracketing of name 'SELECT [Location Name'."

Investigations reveal that the error is caused because Access decides
to do the bloody "smart" thing and reformat my query in it's own
image, so that the part of the query which once read:

FROM a
INNER JOIN (b INNER JOIN c ON ...) ...,
(SELECT [Location Name], [Time Difference] FROM Locations ml WHERE
[Location Name] = [Enter the name of your current location]) AS MyLoc

Now reads...

FROM [SELECT [Location Name], [Time Difference] FROM Locations ml
WHERE [Location Name] = [Enter the name of your current location]]. AS
myLoc, a INNER JOIN (b INNER JOIN c ON ...) ON ...

Digging the newsgroups has revealed that Access does not like
occurences of double brackets ('[[' or ']]') - yet the stupid thing
still decides to do it!

Now, I can solve this problem by simply replacing the '[SELECT' with
'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,

AND HERE IS MY QUESTION:

Surely there has to be a better way around this? Is this solvable, or
am I stuck with this workaround?

I really do not want to have to rewrite this query because:

* This type of logic/structure is used in 30+ different queroes.
* I cannot see how else I can run a subquery to get this information,
and don't see how a LEFT JOIN or other JOIN type can help me obtain
the data (tzd) that I need without 'affecting' the data I return!

And just to finish off... can I please go back to SQL Server now?
I've learnt my lesson!

Apologies for waffling on, if ANYONE can help, I will be eternally
greatful.

Regards,

Andy McNab

[David W. Fenton]

di********@stantonfamily.co.uk (Phil Stanton) wrote in
<3f***********************@mercury.nildram.net>:

Although it dosn't help, to my mind it is best not to have spaces
in any access objects.

In this case it *does* help. The virtual table should work if there
are no brackets needed between the FROM [...]. As ... part of the
declaration.

And it's not just spaces you need to avoid. Also avoid punctuation
marks such as # or ? or any other such characters. I think _ and -
are OK, though, but I never use them in field names, so can't say
for certain.

I use virtual table declarations in my Access SQL on a near daily
basis and do not have any problems with it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On 1 Dec 2003 14:11:13 -0800, an**********@ptc.pilkington.co.uk (Andrew McNab)
wrote:

....

Now, I can solve this problem by simply replacing the '[SELECT' with
'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,
Really? If Access supports this syntax, that's obviously something new, and
it's definitely news to me.
AND HERE IS MY QUESTION:

Surely there has to be a better way around this? Is this solvable, or
am I stuck with this workaround?

How about not putting spaces in table/column names. This is never a good diea
in either Access or SQL Server. There are just too many ways in which using
them makes life harder.

[Andrew McNab]

Thanks for the info guys - as bad as I suspected by the sounds of it.

Unfortunately, I didn't design the database tables, and as this is a
multi-customer database, we are unable to change the design to not use
spaces etc in filed names at this time - don't get me started on that
one!

Also, as my subquery/derived/declared/whatever table contains a
parameter containing both []'s and spacesm, I think I'm buggered from
the start!

Oh well, I have elected to use that nasty workaround - looks like I'm
stuck with it!

Cheers again,

Andy
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<cm********************************@4ax.com>. ..

FYI, it's pretty amazing that Access will convert your query into its own
undocumented syntax for the same thing at all. What you describe is, to my
knowledge, only referred to as a subquery in Oracle parlance, and is referred
to as a declared table in MS SQL Server parlance. In Access, it has no
terminology because it's not documented to exist, though it is used by some of
the Access wizards.

In any case, the Access form of the declared table (or whatever it is) does
not support bracketted expressions within the inner select, so it cannot be
used with table/column names that require brackets such as those with spaces,
etc. This is one of the many reasons to avoice spaces in object names in
Access.

[David W. Fenton]

no****@nospam.nospam (Steve Jorgensen) wrote in
<n6********************************@4ax.com>:

On 1 Dec 2003 14:11:13 -0800, an**********@ptc.pilkington.co.uk
(Andrew McNab) wrote:

...

Now, I can solve this problem by simply replacing the '[SELECT'
with '(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,

Really? If Access supports this syntax, that's obviously
something new, and it's definitely news to me.

I'd heard before that you could replace the "[SELECT ... ]. As"
with "(SELECT ... ). As" but have never had it actually worked.

AND HERE IS MY QUESTION:

Surely there has to be a better way around this? Is this
solvable, or am I stuck with this workaround?

How about not putting spaces in table/column names. This is never
a good diea in either Access or SQL Server. There are just too
many ways in which using them makes life harder.

I don't put spaces in filenames, either.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

an**********@hotmail.com (Andrew McNab) wrote in
<c2*************************@posting.google.com> :

Unfortunately, I didn't design the database tables, and as this is
a multi-customer database, we are unable to change the design to
not use spaces etc in filed names at this time - don't get me
started on that one!
Then write a stored query that aliases the real names to names
without spaces, and use that in your virtual table.
Also, as my subquery/derived/declared/whatever table contains a
parameter containing both []'s and spacesm, I think I'm buggered
from the start!
No, you're not. Just utilize Access for what it's good for.
Oh well, I have elected to use that nasty workaround - looks like
I'm stuck with it!

There's no nasty workaround. Just write a stored query that gets
rid of the offending field names and use that in your SQL. It will
work just fine.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Andrew McNab]

Agreed - using spaces I would never do myself if I'd had a say in the
design of the tables! Again, thanks for your musings.

As for the guy who mentioned about using stored queries instead -
great idea and one that in normal practice I could use, but our
company uses an extra piece of in-house software which reads from our
Access reports, and this extra tool does not know how to read from
Access queries, merely from the RecordSource property containing a
direct 'SELECT...' type statement. Such is life!

Cheers again,

AM
Steve Jorgensen <no****@nospam.nospam> wrote in message news:<n6********************************@4ax.com>. ..

On 1 Dec 2003 14:11:13 -0800, an**********@ptc.pilkington.co.uk (Andrew McNab)
wrote:

...

Now, I can solve this problem by simply replacing the '[SELECT' with
'(SELECT' and ']]. AS myLoc' with ']) AS myLoc', however,

Really? If Access supports this syntax, that's obviously something new, and
it's definitely news to me.

AND HERE IS MY QUESTION:

Surely there has to be a better way around this? Is this solvable, or
am I stuck with this workaround?

How about not putting spaces in table/column names. This is never a good diea
in either Access or SQL Server. There are just too many ways in which using
them makes life harder.