Invalid bracketing of name error?

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
hawkeyexAsked:
Who is Participating?
 
Eric FlammConnect With a Mentor Senior ConsultantCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Eric FlammSenior ConsultantCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Eric FlammSenior ConsultantCommented:
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
 
hawkeyexAuthor Commented:
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
 
Eric FlammSenior ConsultantCommented:
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
 
GRayLCommented:
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
 
hawkeyexAuthor Commented:
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
 
GRayLCommented:
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
 
Eric FlammSenior ConsultantCommented:
Looks good to me - I must have mis-typed in my edit.

-ef
0
 
hawkeyexAuthor Commented:
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
 
GRayLCommented:
You now have a single square bracket before SELECT
0
 
hawkeyexAuthor Commented:
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
 
Steve BinkCommented:
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
 
hawkeyexAuthor Commented:
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
 
Eric FlammSenior ConsultantCommented:
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
 
hawkeyexAuthor Commented:
I don't understand. How do I do this correctly?

Thanks!

David
0
 
hawkeyexAuthor Commented:
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
 
hawkeyexAuthor Commented:
Increasing point value to 500
0
 
Eric FlammSenior ConsultantCommented:
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
 
hawkeyexAuthor Commented:
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
 
hawkeyexAuthor Commented:
Kicking it up for the morning crew.
0
 
Alan WarrenApplications DeveloperCommented:
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
 
hawkeyexAuthor Commented:
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
 
Alan WarrenApplications DeveloperCommented:
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
 
SidFishesConnect With a Mentor Commented:
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
 
Alan WarrenApplications DeveloperCommented:
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
 
Alan WarrenApplications DeveloperCommented:
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
 
hawkeyexAuthor Commented:
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
 
hawkeyexAuthor Commented:
Do you think the "#" is what's confusing VB?

Maybe I should rewrite both tables in a shorter form?

Hawkeye-X
0
 
Eric FlammSenior ConsultantCommented:
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
 
hawkeyexAuthor Commented:
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
 
hawkeyexAuthor Commented:
Well, I am using code, and I also use Expression Builder. I didn't see Query Designer.

0
 
hawkeyexAuthor Commented:
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
 
hawkeyexAuthor Commented:
OK, with the new code, MedianLTC is fixed again.

But MedianVCC still says invalid bracketing. Arrrgh. That REALLY is frustrating.
0
 
Alan WarrenApplications DeveloperCommented:
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
 
hawkeyexAuthor Commented:
Well. Mission accomplished.

The problem was the hashmark.

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

0
 
Eric FlammSenior ConsultantCommented:
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
 
Eric FlammSenior ConsultantCommented:
Good job David - maybe we'll hit it quicker next time.

-ef
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.