Solved

Invalid bracketing of name error?

Posted on 2004-08-25
39
680 Views
Last Modified: 2012-06-21
This is similar to my previous question:
http://www.experts-exchange.com/Databases/MS_Access/Q_21106849.html

Except this time, I tried to do the same query that worked for me (on the bottom, provided by alanwarren), but with a different table.

This error says: "Invalid bracketing of name 'Select Avg([003 (VCC)'

I'm not sure how to interpret that.

The query is here:

SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM [SELECT Avg([003B (VCC)]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)]
   WHERE ((([003B (VCC)].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")
  ]. AS SQ, [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml'));

I am wondering where the problem is at...

Thanks in advance!

Hawkeye-X
0
Comment
Question by:hawkeyex
  • 18
  • 10
  • 5
  • +4
39 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
To avoid this situation entirely, rename your field names that do not have spaces or punctuation marks, then you wouldn't need square brackets [ ].
For examples, names like StartDate, EndDate, TotalColiformPer100Ml, 003B_VCC, etc.

Even better, use Hungarian notation based on the field type, for example dt_start, dt_end, sng_total_coliform_per_100ml, etc.

Hope this helps.
-Jim
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
I think the problem is that you have a table with () in the name, and SQL sees the () as separators; so it doesn't make it to ] in your Select clause.

You could make this easier if you used a table alias, like so:


SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM [SELECT Avg([tbl]!Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ((([tbl].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")] AS SQ, [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml'));


Also, I deleted a "." from the middle of the third to last line (after the "]") - that might be the problem as well...

-ef
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
here is Alan's code:

SELECT
  IIf(SQ.Expr1="","",Median('001A (LTC)','Total Coliform #/100ml')) AS Expr3,
  Format(Date,"yyyy-mm") AS Expr4
FROM
  (SELECT Avg([001A (LTC)]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [001A (LTC)]
   WHERE ((([001A (LTC)].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")
  ) SQ,
     [001A (LTC)]

WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('001A (LTC)','Total Coliform #/100ml'));

Note that he uses parentheses, NOT square brackets, around the sub-query - square brackets are not interchangeable in SQL - they are used to surround table and field names, not sub-queries or binary operations - use parens like Alan showed.

-ef
0
 

Author Comment

by:hawkeyex
Comment Utility
eflamm:

That still doesn't solve the first problem -

Invalid bracketing of name 'Select Avg([tbl'

And I used this:

 SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM [SELECT Avg([tbl]!Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ((([tbl].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")] AS SQ, [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml'));
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
That still needs to be a "(" in front of SELECT on line 2, and a ")"  before AS SQ. Also, I don't understand what the [003b (VCC)] at the end of that line does. Here's how i read the query:

Select Expr3, Expr4 from
   (select expr1, expr2 from tbl where date between start and end group by date) as sq
where date between start and end group by date, expr3

I suggest you dump the last GROUP BY clause - it's useless - the subquery will return 1 record per month for each month in the range of dates - so there's no need to group by month or measure in the outer query. You also don't need the where clause in the outer query - the data is already restricted to the date range in the subquery.

 SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM (SELECT Avg([tbl]!Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")) AS SQ;

-ef
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Look at your first line:
SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format

Bracketing in:
 Median('003B (VCC)'   Something is wrong here I believe.
Should be:
'Median(003B (VCC)'   ???
0
 

Author Comment

by:hawkeyex
Comment Utility
OK. Eflamm, now it returns the following error:

Syntax error (missing operator) in query expression 'Avg[tbl]!Total Coliform #/100ml])'.

This is what I used for query:

 SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM (SELECT Avg([tbl]!Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")) AS SQ;

I'm a SQL newbie as well as Access. These are not my baliwick, because I mostly do web design and my boss dumped this project to me, and I have to learn via trial by fire. And I get burnt every time. Appreciate your help.

Thanks!

David
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Change:

FROM (SELECT Avg([tbl]!Total Coliform #/100ml]) AS Expr1

To:

FROM (SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1

Note the opening sq bracket before Total
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Looks good to me - I must have mis-typed in my edit.

-ef
0
 

Author Comment

by:hawkeyex
Comment Utility
Back to the Invalid Bracketing error.

Error:
Invalid bracketing of name 'Select Avg([tbl'

Here's the SQL now..
SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM [SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")]. AS SQ;
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
You now have a single square bracket before SELECT
0
 

Author Comment

by:hawkeyex
Comment Utility
SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")]. AS SQ;


Updated, and still gives a error, albeit a different one.

Syntax error in FROM clause.

Hmm...
0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
I do not understand why you keep removing the parenthesis from the sub-query:

SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM (SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")) AS SQ;
0
 

Author Comment

by:hawkeyex
Comment Utility
OK. Using the new query results in a VBA error:

SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM (SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")) AS SQ;

The error says: Run-time error '3061' - Too few parameters. Expected 1.

And the debug points at this VBA code:

  Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];")

Any help would be appreciated.

David
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
I think there's a problem in your select - the SQ returns Expr1, Expr2, but your selecting a function of Expr1, and a function of Date - which doesn't exist in SQ. Change
 >Format(Date,"yyyy-mm") as Expr4<
to
Expr2
 in the select (no need to alias an alias)

The 3061 is probably because you used Date without brackets and VBA tried to run the Date function and couldn't find the parameters. - but if you included the [], SQL would have failed.

-ef
0
 

Author Comment

by:hawkeyex
Comment Utility
I don't understand. How do I do this correctly?

Thanks!

David
0
 
LVL 8

Accepted Solution

by:
Eric Flamm earned 250 total points
Comment Utility
SELECT Expr2, IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3
FROM (SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")) AS SQ;

I moved expr2 (which is the date group field from the subquery) to the first column, followed by the Expr3 calculation - you know you can use meaningful aliases instead of ExprX - so you could say:


SELECT Expr2 as DateGroup, IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS DataValue
FROM (SELECT Avg([tbl]![Total Coliform #/100ml]) AS Expr1, Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)] as tbl
   WHERE ([tbl].[Date] Between [Start Date] And [End Date])
   GROUP BY Format([Date],"yyyy-mm")) AS SQ;

Notice that I never use Date as a fieldname or alias - it's a reserved word in SQL and Access, which means the processing engine tries to do something with the word, rather than treating it as a label. You can put reserved words in [], but it gets really confusing.

-ef
0
 

Author Comment

by:hawkeyex
Comment Utility
I'm getting nowhere on this.

Let's start over.

Here is the current Excel formula for this problem

=IF('CURRENT DMR'!X35="","",MEDIAN('CURRENT DMR'!X4:X34))

'CURRENT DMR'!X35 is equivalent to [AvgVCCTotalColi]

There is a Median module in place (see other post)

'CURRENT DMR'!X4:X34 is equivalent of [003B (VCC)].[Total Coliform #/100ml] restricted by date (by month, actually)

AvgVCCTotalColi looks like this:

SELECT Avg([003B (VCC)]![Total Coliform (#/100ml)]) AS Expr1, Format(Date,"yyyy-mm") AS Expr2
FROM [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

I hope you understand now.

Hawkeye-X
0
 

Author Comment

by:hawkeyex
Comment Utility
Increasing point value to 500
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Gotta go cook dinner - will return in about an hour - maybe we can get this fixed.

Am I correct in saying you imported your spreadsheet into Access, or copied the data?

Also, what do you mean - "restricted by month" - are Start Date and End Date always only 1 month? If so, the Group By clause needs to be changed, or dropped.

Anyway, I'll be back shortly.

-ef
0
 

Author Comment

by:hawkeyex
Comment Utility
OK. That's fine. I have to leave in a hour, we can wrap this up tomorrow, hopefully.

I imported the spreadsheet data into Access.

Well, it depends on the days in a month. like Jan - 31 days, Feb, 28 days (if leap, 29), etc. etc.

David
0
 

Author Comment

by:hawkeyex
Comment Utility
Kicking it up for the morning crew.
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi hawkeyex,

This seems to be working ok, I created a copy of the table from the other question table![001A (LTC)] as table![003B (VCC)]

Then ran the folowing sql which calls your Median function posted in the last question, and it seems to run ok on my machine.

Returns:
Expr3      Expr4
3      2004-07

SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM [SELECT
     Avg([003B (VCC)]![Total Coliform #/100ml]) AS Expr1,
     Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)]
   WHERE ((([003B (VCC)].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")
  ]. AS SQ, [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml'));

The obvious thing was the square brackets around the sub-select and the "AS SQ" after the sub-select should just be SQ without the "AS"

Weirs thing about the square brackets, Im positive I put changed them to round brackets and access is changing them to square brackets after first execution; hmmm...


Alan
0
 

Author Comment

by:hawkeyex
Comment Utility
Weird. When I try to save the above query, I get an error that says:

"Invalid bracketing of name 'Select Avg ([003B (VCC)'.

Not sure why.

0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hey this is strange, got a query query2 which is running fine, but if I copy the sql from query2 and paste into a new query in sql view I am getting the same error that you are getting, hmmm. But query2 runs, I kid you not!!!



0
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 250 total points
Comment Utility
what i would suggest is to deal with the sql creation bit by bit using variables...with the number of "bad" characters you've got in there (spaces () # etc) it doesn't surprise me that access is confused...additionally it makes troubleshooting this difficult as it is so hard to read...

of course you need to be doing this in code...so i don't know if that works for you

also i'm not sure what you are trying to do here

 Median('003B (VCC)','Total Coliform #/100ml') with the single quotes here, you are defining these a literal string and not a value and then trying to run a function on them...

don't you mean

 Median([003B (VCC)]![Total Coliform #/100ml])

just like

Avg([003B (VCC)]![Total Coliform (#/100ml)])

????


0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
if you change the brackets around the sub-select to round brackets and remove the AS it saves fine.

SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM (SELECT
     Avg([003B (VCC)]![Total Coliform #/100ml]) AS Expr1,
     Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)]
   WHERE ((([003B (VCC)].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")
  ) SQ, [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml'));
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
the previous post I posted will run fine before you save it, but when you save it Access modifies the round brackets to square brackets with a dot trailer and inserts an AS keyword so it looks like this next time you open it in sql view.

SELECT IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM [SELECT
     Avg([003B (VCC)]![Total Coliform #/100ml]) AS Expr1,
     Format([Date],"yyyy-mm") AS Expr2
   FROM [003B (VCC)]
   WHERE ((([003B (VCC)].[Date]) Between [Start Date] And [End Date]))
   GROUP BY Format([Date],"yyyy-mm")
  ]. AS SQ, [003B (VCC)]
WHERE ((([003B (VCC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('003B (VCC)','Total Coliform #/100ml'));


Not really sure what is going on here David.

Alan
0
 

Author Comment

by:hawkeyex
Comment Utility
OK. With that, I get a VBA error:

Run-time error '3061':

Too few parameters. Expected 1.

So I debug:

and it highlights here:

  Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];")

Not sure what's going on here, either.
0
 

Author Comment

by:hawkeyex
Comment Utility
Do you think the "#" is what's confusing VB?

Maybe I should rewrite both tables in a shorter form?

Hawkeye-X
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Sid - the literals are arguments to a user-defined function which works like the DSUM function in VBA.

Alan - The AS keyword is optional - Access just puts it in there for readability.

David - back to yesterday's notes - is the data in the table for only 1 month at a time? If so, you don't really need the where clause and Group By clause in the subquery. You certainly don't need the Group By in the outer query, because the data is already grouped by month in the subquery.

I agree with Sid, using Aliases  would really make this easier to read.

By the way, are you doing this in code, or in Query Designer in Access? If in Query Designer, I suggest you build the SubQuery as 1 query, then build the outer query as a 2d query based on the first query. If that sounds good, speak up and I'll try to walk you through it.

-ef
0
 

Author Comment

by:hawkeyex
Comment Utility
I rewrote Total Coliform #/100 ml to Total_Coliform and now both MedianLTC (previous question)
and MedianVCC is messed up.

I'm going to attempt to rewrite the original query from the previous question and see how it works. *IF* I also have to get rid of ()'s from (LTC) and (VCC), then I'll do it.

Hawkeye-X

0
 

Author Comment

by:hawkeyex
Comment Utility
Well, I am using code, and I also use Expression Builder. I didn't see Query Designer.

0
 

Author Comment

by:hawkeyex
Comment Utility
Well, I am using code, and I also use Expression Builder. I didn't see Query Designer.

As far as dates go, it counts by the days of the month. Like Janurary is 31 days, Feb, 28 days (leap year 29), March 31, etc.

David
 
0
 

Author Comment

by:hawkeyex
Comment Utility
OK, with the new code, MedianLTC is fixed again.

But MedianVCC still says invalid bracketing. Arrrgh. That REALLY is frustrating.
0
 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
Hi eflamm,

yeah I got the 'AS' as optional, what I dont get is access changing the round brackets that encapsulate the sub-select to square brackets with a DOT after the closing square bracket at first run time. Then if you close the query and open it again you get an "invalid bracketing error" ;;;; the title of this question.

So the problem seems to be the use of standard operators in the field and table names with a peppering of reserved words to boot. Access seems to be doing its best to FIX the ambiguities, but is in fact buggering it up.

Arrrrrrggghhhh!!!

Alan

 
0
 

Author Comment

by:hawkeyex
Comment Utility
Well. Mission accomplished.

The problem was the hashmark.

I'll split the points to eflamm and sidfishes for ideas and query help.

0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Query designer is on the queries tab in Access - you use the grid to select fields and set groupings and filters. You can then see the SQL Access generates in SQL view, but it's not always pretty. Anyway, you're using code, so the sub-query is more efficient. Sounds like you're fixing up the field and table names; I don't really see why that messes up your functions if you're passing the corrected field and table names.

You still have the word Date without brackets in your query - that's where the Error 3601 comes from

-ef
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Good job David - maybe we'll hit it quicker next time.

-ef
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now