473,701 Members | 2,731 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Invalid bracketing of name / subquery problem - PLEASE HELP!

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
Nov 12 '05 #1
8 19594
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**********@p tc.pilkington.c o.uk> wrote in message
news:6d******** *************** ***@posting.goo gle.com...
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

Nov 12 '05 #2
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**********@pt c.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
unnecessaril y 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'."

Investigatio ns 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


Nov 12 '05 #3
di********@stan tonfamily.co.uk (Phil Stanton) wrote in
<3f************ ***********@mer cury.nildram.ne t>:
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
Nov 12 '05 #4
On 1 Dec 2003 14:11:13 -0800, an**********@pt c.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.

Nov 12 '05 #5
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.

Nov 12 '05 #6
no****@nospam.n ospam (Steve Jorgensen) wrote in
<n6************ *************** *****@4ax.com>:
On 1 Dec 2003 14:11:13 -0800, an**********@pt c.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
Nov 12 '05 #7
an**********@ho tmail.com (Andrew McNab) wrote in
<c2************ *************@p osting.google.c om>:
Unfortunatel y, 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
Nov 12 '05 #8
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**********@pt c.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.

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
17480
by: Glenn A. Harlan | last post by:
Why am I receiving the below error when calling - Path.GetTempFileName() The directory name is invalid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IO.IOException: The directory name is invalid.
1
4174
by: Andrew McNab | last post by:
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...
7
2369
by: K. Crothers | last post by:
I administer a mechanical engineering database. I need to build a query which uses the results from a subquery as its input or criterion. I am attempting to find all of the component parts of which a part may be composed. I have a table of parts and their subparts. The problem is that each of those subparts may be composed of smaller component parts. The subpart would then be listed in the Part field linked to each of its subparts in...
9
6647
by: MR | last post by:
I get the following Exception "The data at the root level is invalid. Line 1, position 642" whenever I try to deserialize an incoming SOAP message. The incoming message is formed well and its length is 642 bytes ( I have appended it to the end of this message). I suspect that the reason may have something to do with an incorrect declaration of which class to de-serialize to. In the attached code I substituted @@@@@@@ in the code below with...
2
1938
by: reap76 | last post by:
I am running the following query: if (select IntExternalAccountID from ExternalAccount) = (select IntExternalAccountID from InternalAccount) select * from InternalAccount where AccountPurpose=2 and getting the following error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. What I am attempting is if the values in ColumnA from...
3
2919
by: Shals | last post by:
Hi, I'm using Form and a subform control within that form. Main form is getting data from one table(Buildings) and subform is getting data from another table(BldgDataByFloor). but both the form and subform are linked through a child field which is common in both the tables(BuildingNumber). Now, whenever I close the form I receive an error "Subquery returned more than 1 value. This is not permitted when the
0
2062
by: kiran2nirvan | last post by:
hi please help in solving this i am recieving this error"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." for the code CREATE procedure . (@frmMasterID int,
5
8028
by: Anne | last post by:
Hello! Here is the statement in question: --STATEMENT A SELECT * FROM dbo.myTable WHERE colX in (SELECT colX FROM dbo.sourceTable) The problem with Statement A is that 'colX' does not exist in 'dbo.sourceTable'. It does, however, certainly exist in 'dbo.myTable'. Breaking the statement down, we have:
4
6333
by: mattehz | last post by:
Hey there, I am trying to upload old source files and came across these errors: Warning: Invalid argument supplied for foreach() in /home/mattehz/public_html/acssr/trunk/inc_html.php on line 59 Notice: Undefined index: args in /home/mattehz/public_html/acssr/trunk/inc_error.php on line 92 Warning: Invalid argument supplied for foreach() in /home/mattehz/public_html/acssr/trunk/inc_error.php on line 92
0
8736
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9229
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8977
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7824
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6571
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4662
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3102
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2398
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.